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
