Ooops! You just threw a new wrench into the works! "A video may have multiple titles." In this case you must distinguish the original from its presumably foreign-translations (or whatever). In other words, you might store the master as "La Cage aux Folles" and the English subtitled version as something else, which implies another 1:M table, residing above Titles in the hierarchy. For simplicity let's call it Videos, and then rename Titles to VideoTitles, giving it a foreign key referencing Videos. Thus in the Videos table we have entry 12345, "La Cage Aux Folles" and in the VideoTitles table we have an entry "The Cage of Fools" or whatever, with an FK referencing 12345.
A title search would then require interrogation of two tables, which in itself isn't terrible since one could UNION the titles from both tables, but possibly this is not the best model. Perhaps the Videos table should contain no Title info, and all that data should be exported to the Titles table. I'd have to give that some thought before deciding. > 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? The database knows nothing except what is permitted. (Hope Dostoevsky didn't design the database:-) You front end application should anticipate this and present pretty controls for the user. The table TitleGenres implies a reference to both Titles and Genres, so each of these could be presented as a dropdown list, or in a web app you could pass in the PK from the Titles table and have your insert code write the received parameter into the table. (By sticking to Titles rather than shifting the relationship to Videos, you could account for the strange belief in France that Jerry Lewis is funny :-) Creating a junction table is simple. Just add the columns you need and declare them as foreign keys, specifying their reference-tables (InnoDB). If you're using MyISAM tables the responsibility falls entirely on your front end. You may want to modify the Stars table to reference Videos rather than Titles, since the stars remain the same even if you translate the film into Russian or Swahili. hth, Arthur ----- Original Message ----- From: "John Chang" <[EMAIL PROTECTED]> To: "Arthur Fuller" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, October 14, 2002 7:26 PM Subject: Re: Normalization sql > > >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 > --------------------------------------------------------------------- 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
