Would someone review my schema below and see if it makes sense?
I am developing a movie review db and the relationship I am concernced about is the many to many relationship between the actors and movies and one to many relationship betweent the producers and directors and their movies.
The db will be used to create a publication that will include the following on a page:
Title
Tag line
Rating stars
Genre
Theatre rating
Length
Profanity
Sex/nudity
Violence
Drugs/alcohol
Action (text to describe the type of action)
Comedy (text to describe the type of comedy)
Year
Synoptic Review
Critic's Review
Listing of Actors along with other movies they have been in
Release date (relative to the status below)
Release Status (Theatre, then DVD & Video, then pay per view, then TV)
Actors with a list of other movies they have been in
Producer with list of other movies he/she has produced
Director with list of other movies he/she has directed
Please ignore the field and table naming conventions - it isn't the way they will be set up but this is for demonstration purposes for you all to understand it.
MOVIES
MovieID (primary key, autonumber)
Title (text)
TagLine (text)
Year (long integer)
Genre (text)
Theatre rating
Rating stars (long integer)
Length (text)
Profanity (text)
Sex/nudity (text)
Violence (text)
Drugs/alcohol (text)
Action (text)
Comedy (text)
RELEASE DATE
ReleaseID (primary key, autonumber)
MovieID (foreign key to Movies)
Release date (date/time)
ReleaseStatus (text)
REVIEWS
?This may end up as a many-to-many relationship down the road too
ReviewID (primary key, autonumber)
MovieID (foreign key to Movies)
Critical (memo or link to external file)
Synopsis (memo)
PEOPLE
PeopleID (primary key, autonumber)
different tables? [/COLOR]?[/I]
FirstName (text)
LastName (text)
PEOPLETYPE
?Do I need a key for this table?
PeopleTypeID (primary key, autonumber)
PeopleType (text)
(actor, producer director - would it be better to maintain separate tables?
PeopleID (foreign key to People)
MovieID (foreign key to movies)
So the big issue is the people related to this movie are also related to several other movies. For example if the review is for Pirates of the Caribbean the actors, the director and producer will be listed as follows:
JOHNNY DEPP From Hell, Blow, Before Night Falls, Chocolat
GEOFFREY BUSH The Banger Sisters, Quills, House on Haunted Hill
Director: GORE VERBINSKI The Ring, The Mexican, Mouse Hunt
Producer: JERRY BRUCKHEIMER Kangaroo Jack, Black Hawk Down, Pearl Harbor
*******************
2nd Question
Also the critical review can be very long say 800 words or so. Could I link the field to an external word file or something? How would I do that?
3rd Question
How will I output the multiple Movies in the text file separated by commas that can then be used by the publisher in their desktop publishing template.
TIA
ML
I am developing a movie review db and the relationship I am concernced about is the many to many relationship between the actors and movies and one to many relationship betweent the producers and directors and their movies.
The db will be used to create a publication that will include the following on a page:
Title
Tag line
Rating stars
Genre
Theatre rating
Length
Profanity
Sex/nudity
Violence
Drugs/alcohol
Action (text to describe the type of action)
Comedy (text to describe the type of comedy)
Year
Synoptic Review
Critic's Review
Listing of Actors along with other movies they have been in
Release date (relative to the status below)
Release Status (Theatre, then DVD & Video, then pay per view, then TV)
Actors with a list of other movies they have been in
Producer with list of other movies he/she has produced
Director with list of other movies he/she has directed
Please ignore the field and table naming conventions - it isn't the way they will be set up but this is for demonstration purposes for you all to understand it.
MOVIES
MovieID (primary key, autonumber)
Title (text)
TagLine (text)
Year (long integer)
Genre (text)
Theatre rating
Rating stars (long integer)
Length (text)
Profanity (text)
Sex/nudity (text)
Violence (text)
Drugs/alcohol (text)
Action (text)
Comedy (text)
RELEASE DATE
ReleaseID (primary key, autonumber)
MovieID (foreign key to Movies)
Release date (date/time)
ReleaseStatus (text)
REVIEWS
?This may end up as a many-to-many relationship down the road too
ReviewID (primary key, autonumber)
MovieID (foreign key to Movies)
Critical (memo or link to external file)
Synopsis (memo)
PEOPLE
PeopleID (primary key, autonumber)
different tables? [/COLOR]?[/I]
FirstName (text)
LastName (text)
PEOPLETYPE
?Do I need a key for this table?
PeopleTypeID (primary key, autonumber)
PeopleType (text)
(actor, producer director - would it be better to maintain separate tables?
PeopleID (foreign key to People)
MovieID (foreign key to movies)
So the big issue is the people related to this movie are also related to several other movies. For example if the review is for Pirates of the Caribbean the actors, the director and producer will be listed as follows:
JOHNNY DEPP From Hell, Blow, Before Night Falls, Chocolat
GEOFFREY BUSH The Banger Sisters, Quills, House on Haunted Hill
Director: GORE VERBINSKI The Ring, The Mexican, Mouse Hunt
Producer: JERRY BRUCKHEIMER Kangaroo Jack, Black Hawk Down, Pearl Harbor
*******************
2nd Question
Also the critical review can be very long say 800 words or so. Could I link the field to an external word file or something? How would I do that?
3rd Question
How will I output the multiple Movies in the text file separated by commas that can then be used by the publisher in their desktop publishing template.
TIA
ML
