Author and movies (many to many relation) database question...

Hi to all,
first of all I apologise if there is already someone that asked the
same thing.
I have 3 entities (tables).
- AUTHOR (id_author, author_name, birth_date, biography)
- AUTHOR_FUNCTION (id_function, function),
- MOVIE (id_movie, movie_title, year...).
Their relations:
1.) Each author can work in many movies
2.) Each author can have many functions in different movies.
3.) Each author can have many functions in the same movie.
4.) Each movie can have many authors.
And here comes the question:
I already know that I have to create a "middle" entity/table
ex.AUTHOR_FUNCTION_MOVIE (id_movie, id_author, id_function) but is
there any other way to solve the many-many relation? I don't like very
much the idea to have so much entries in the table - in fact the same
author&movie can be listed many times because of its different
function...
Thank you in advance for your help:-)> there any other way to solve the many-many relation? I don't like very
> much the idea to have so much entries in the table - in fact the same
> author&movie can be listed many times because of its different
> function...
Why ? It is a classic many-to-many soultion to have a "junction" table
Actually, if you need some searching or other issues that we do not know you
may try to denormalize tables
<Peter.Laganis@.gmail.com> wrote in message
news:1147685467.399149.310320@.v46g2000cwv.googlegroups.com...
> Hi to all,
> first of all I apologise if there is already someone that asked the
> same thing.
> I have 3 entities (tables).
> - AUTHOR (id_author, author_name, birth_date, biography)
> - AUTHOR_FUNCTION (id_function, function),
> - MOVIE (id_movie, movie_title, year...).
> Their relations:
> 1.) Each author can work in many movies
> 2.) Each author can have many functions in different movies.
> 3.) Each author can have many functions in the same movie.
> 4.) Each movie can have many authors.
> And here comes the question:
> I already know that I have to create a "middle" entity/table
> ex.AUTHOR_FUNCTION_MOVIE (id_movie, id_author, id_function) but is
> there any other way to solve the many-many relation? I don't like very
> much the idea to have so much entries in the table - in fact the same
> author&movie can be listed many times because of its different
> function...
> Thank you in advance for your help:-)
>|||Yes I know, but ex.if I have 20.000 movies and 5.000 authors and 15
functions the no of entries in the "junction" table can be a lot. Will
not that impact on the aplication response time?|||Hi
No, it should not. I have been working on very large database which has a
junction table that contains 50 mln rows. If you have properly defined
indexes on the tables it should not be an issue at all
<Peter.Laganis@.gmail.com> wrote in message
news:1147687257.391706.17460@.i40g2000cwc.googlegroups.com...
> Yes I know, but ex.if I have 20.000 movies and 5.000 authors and 15
> functions the no of entries in the "junction" table can be a lot. Will
> not that impact on the aplication response time?
>

Author and movies (many to many relation) database question...

Hi to all,
first of all I apologise if there is already someone that asked the
same thing.
I have 3 entities (tables).
- AUTHOR (id_author, author_name, birth_date, biography)
- AUTHOR_FUNCTION (id_function, function),
- MOVIE (id_movie, movie_title, year...).
Their relations:
1.) Each author can work in many movies
2.) Each author can have many functions in different movies.
3.) Each author can have many functions in the same movie.
4.) Each movie can have many authors.
And here comes the question:
I already know that I have to create a "middle" entity/table
ex.AUTHOR_FUNCTION_MOVIE (id_movie, id_author, id_function) but is
there any other way to solve the many-many relation? I don't like very
much the idea to have so much entries in the table - in fact the same
author&movie can be listed many times because of its different
function...
Thank you in advance for your help:-)> there any other way to solve the many-many relation? I don't like very
> much the idea to have so much entries in the table - in fact the same
> author&movie can be listed many times because of its different
> function...
Why ? It is a classic many-to-many soultion to have a "junction" table
Actually, if you need some searching or other issues that we do not know you
may try to denormalize tables
<Peter.Laganis@.gmail.com> wrote in message
news:1147685467.399149.310320@.v46g2000cwv.googlegroups.com...
> Hi to all,
> first of all I apologise if there is already someone that asked the
> same thing.
> I have 3 entities (tables).
> - AUTHOR (id_author, author_name, birth_date, biography)
> - AUTHOR_FUNCTION (id_function, function),
> - MOVIE (id_movie, movie_title, year...).
> Their relations:
> 1.) Each author can work in many movies
> 2.) Each author can have many functions in different movies.
> 3.) Each author can have many functions in the same movie.
> 4.) Each movie can have many authors.
> And here comes the question:
> I already know that I have to create a "middle" entity/table
> ex.AUTHOR_FUNCTION_MOVIE (id_movie, id_author, id_function) but is
> there any other way to solve the many-many relation? I don't like very
> much the idea to have so much entries in the table - in fact the same
> author&movie can be listed many times because of its different
> function...
> Thank you in advance for your help:-)
>|||Yes I know, but ex.if I have 20.000 movies and 5.000 authors and 15
functions the no of entries in the "junction" table can be a lot. Will
not that impact on the aplication response time?|||Hi
No, it should not. I have been working on very large database which has a
junction table that contains 50 mln rows. If you have properly defined
indexes on the tables it should not be an issue at all
<Peter.Laganis@.gmail.com> wrote in message
news:1147687257.391706.17460@.i40g2000cwc.googlegroups.com...
> Yes I know, but ex.if I have 20.000 movies and 5.000 authors and 15
> functions the no of entries in the "junction" table can be a lot. Will
> not that impact on the aplication response time?
>

Attribute relation error

Hi! I have changed some attribut relations in a time dimension user hierarchy(SSAS2005 SP1), from flexible to rigid. When I try to deploy this change to the server the whole process stops with these strange error messages:

"Error 1 File system error:
Error opening file; \\?\D:\MSSQL\MSSQL.2\OLAP\Data\SalesStatistics.0.db\Sales Statistics 2.0.cub\Sales Master Cube.0.det\Sales Master Cube.0.prt\35.agg.rigid.data
is not a disk file or file is not accessible."

The next error repeats several times for all named sets I have built for the time dimension:

"Error 2 MdxScript(Sales Statistics Facilities) (174, 6)
The dimension '[RecentMonth]' was not found in the cube when the string, [RecentMonth], was parsed. 0 0 "

[RecentMonth] is actually a named set, that exist, with the following code:

Tail(Filter([Time].[Time_Calendar].[Month].Members,

([Time].[Time_Calendar].Currentmember,[Measures].[Sales SEK])>0))

My other named sets use this set as a reference like Last12Months and so on.

It is the first named set in a collection of named sets. All named sets are placed last in the scripting tab i BI-Dev Studio.

Any ideas?

Regards

Thomas Ivarsson

Problem is solved. I removed the named sets for the time dimension, processed the cube without them and processed again with the the same named sets.

SSAS 2005 probably use an internal identifier for the dimension that the named sets are mapped to. Changing attribut relation types probably means a new identifier/version of the dimension.

/Thomas Ivarsson