Yes, at first it may seem that groups are just another form of artist, and that your searches would be simpler, but if you put them both in a single table, how do you model someone like say Paul McCartney, who was in the Beatles and Wings? Or Yo Yo Ma, who has guested with every important symphony orchestra in the world? Particularly in the jazz world, your model poses enormous problems. How could you model Charlie Haden, Miles Davis, the Brecker brothers, and so so?
Peter's right. If you want a robust model, you have to break out groups and artists. If you want a historically accurate model, you may need a junction table GroupArtists, with FKs into Groups and Artists, and a pair of date columns indicating "from" and "to" -- which could overlap, again especially in jazz. Arthur -----Original Message----- From: Peter Brawley [mailto:peter.brawley@;artfulsoftware.com] Sent: Saturday, October 26, 2002 6:43 AM To: Franklin Williams Jr.; [EMAIL PROTECTED] Subject: Re: self relation query help Franklin, the "basic relational rules" (http://hometown.aol.com/mbaddenda/art120.html, http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node1.html) say, decompose relationships like your artists & groups to separate tables, and your example fits right in. PB ----- ----- Original Message ----- From: "Franklin Williams Jr." <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 25, 2002 9:44 PM Subject: self relation query help > I have a table with names in it where the names can function as 1 of 2 things - > either an artist or a group. In order to make searching easy I wished to keep > these names in one table. EG: searching for artist name, whether a group or > soloist you would have to search only one table. The problem arises if the > person wishes to get the names of the artists in the group or an artist also > performs in multiple groups. I thought a self relationship of sorts solved this > but then I could not figure out how to write the query. For instance the > simplest query is: show me all the groups and artists in each group, or Show me > the artists names in group U2. Easy if groups and artist names were in 2 tables, > however... > > A table B table > ---------------------------- ------------------------------ > id Name id grp_id art_id > ---------------------------- ------------------------------ > 1 Joe 1 2 1 > 2 U2 2 2 3 > 3 Mike 3 2 4 > 4 John ------------------------------ > 5 Jane > --------------------------- > > In the above sample data, table B is a "join" of sorts where the A.id functions > as both the B.grp_id and B.art_id. EG: B.grp_id = A.id AND B.art_id = A.id > I thought I had come up with a nifty little trick (using the same id in > different fields to designate a distinction ) but alas..... > I can only select one column to get the names of the group and artists. I > thought nested selects would solve this - maybe not - but no nested selects in > mysql anyway. > So..can this be done with this design or must I split the artists and groups > into separate tables, forcing a multiple table search? I hoped column alias > would solve this but I could not make it work using them. Confused? Good...so am > I! Thanks in advance for any help > > Franklin Williams > [EMAIL PROTECTED] > > > --------------------------------------------------------------------- > 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
