This is the classic case for a junction table (aka bridge table, associative table). It sits between Artists and Songs; let's call it PlaysOn.
Artists: ArtistID: autoincrement ArtistName: varchar DOB: date etc. Songs: SongID: autoincrement SongName: varchar Duration: in seconds, say etc. PlaysOn: SongID: FK into Songs ArtistID: FK into Artists Given this structure you can: a) add any number of players to a song; b) select any song and all its players; c) select any player and all the songs she played on; d) select a song title and see all the versions you have; e) add another layer atop this called Recordings without disturbing anything beneath (now the same song by the same artist(s) can appear on multiple recordings). You could add a ComposerID (FK into Artists) to the Songs table, but that corners you because what do you do about Lennon & McCartney, Rogers & Hammerstein and so on? To go to that level of detail you need another bridge table, SongComposers: SongComposers: SongComposerID: autoincrement SongID: FK into Songs ArtistID: FK into Artists With this table, you could add to the list of selects with a union that grabs any song Artist 123 either played on or helped compose. hth, Arthur ----- Original Message ----- From: "Robbie Newton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 17, 2002 10:25 PM Subject: [MANY to MANY] relationship with MySQL ??? > Hello all, > > [~~I guess I could do something like MySQL>select FROM "Songs" where > "artists" contains $currentArtist~~] > > I have a sampling section on a site that I am working on and am trying to > find out how to display a table of the artists featured in that song. The > sample section works as follows: > > Go to the samples page and choose an album to sample. > Returns the list of song names that result from the album you chose > click on one of the song names to open a new window with the shockwave file > that streams the song. > > The client has asked me to add the (one or many) artists featured in that > song in the window that loads up. I am not sure how to set this up. It would > be a many to many relationship I think... > > <Artists Table> (one given artist could be featured on many different songs) > [many] > to > <Songs Table> (one given song could have many featured artists) [many] > > My first thout of how to set this up is to just enter comma delimited data > into a field in the song database called, "artists". But when I query it I > don't know how I could make that work. I guess I could do something like > MySQL>select FROM "Songs" where "artists" contains $currentArtist< Would > that work? Is there a such thing as "contains". (kinda new to SQL). > > Thanks for your help, > > Robbie > > --------------------------------------------------------------------- > 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
