>Studios (StudioName, StudioID) >Genres (GenreName, GenreID) >Titles (VideoTitle, Details, StudioID, GenreID, BitRateID, TitleID) >Actors (F_Name, L_Name, ActorID) >Stars (TitleID, ActorID) TitleGenres (TitleID, GenreID)
So, if I just use theses tables I will be OK if the video has multiple titles, multiple actors, & multiple genres? How do I create a junction table? Example, between Genres and Titles, how do I add the to PK fields to TitleGenres? Do I just create the TitleGenres table and add the TitleID & GenreID field and does the database know they are linked? Thank you. At 09:50 AM 10/14/2002 -0400, Arthur Fuller wrote: >You're starting off ok, just missing the links between the tables. I would >suggest for simplicity though that you change the names of your PKs to >reflect their table, i.e. Title (VideoTitle, Details, TitleID). Otherwise >once you do multi-table queries joins you will have to specify the table >names every time. > >A studio makes zero or more titles (videos). >A film belongs to zero or more genres (your answer may be that a film >belongs to precisely one genre) >A title stars one or more actors (you may plan on storing only one, but how >will you decide in a case such as Eight Women?) I would say you need a >junction table between Titles and Actors so you can handle multiple stars. >I'm not sure what BitRate means but I guess it is an attribute of the Title. > >Studios (StudioName, StudioID) >Genres (GenreName, GenreID) >Titles (VideoTitle, Details, StudioID, GenreID, BitRateID, TitleID) >Actors (F_Name, L_Name, ActorID) >Stars (TitleID, ActorID) > >If you decide that a Title can belong to more than one Genre, then you need >a junction table there too: > >TitleGenres (TitleID, GenreID) > >This change would involve removing the GenreID from the Titles table. > >This design lets you easily find all the titles made by a studio, all the >titles featuring a given actor (even if she is the co-star rather than the >star), all the titles in a genre and even compound queries such as all the >MGM thrillers featuring actor XYZ. > >hth, >Arthur > >----- Original Message ----- >From: "John Chang" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Monday, October 14, 2002 8:49 AM >Subject: Normalization sql > > > > > > I've read a bunch about normalization in MySQL and still can't do it very > > well. What I want to normalize is videos (Title, Studios, Actors, Genre, > > &bitrate). > > > > These are the tables and fields I think it needs. Is this > > normalized? Thank you. > > Table (Fields) > > Title (VideoTitle, details, id) > > Studio (Name, id) > > Actors (F_Name, L_Name, id) > > Genre (Name, id) > > Bitrate (rate, id) > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
