browno

OK! Thanks for all your help!

[!--quoteo(post=335329:date=Jan 10 2006, 11:57 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 11:57 PM) 335329[/snapback][/div][div class=\'quotemain\'][!--quotec--] Well, if all artists have a band_id, then you might as well link it to artist_id, and find the band from there. Alternatively, you can put in whatever uid you want, and have a type field. Personally, I prefer the former, because it's better DB design. My head hurts! If i have: ARTISTMAP artistmap- bigint(20), unsigned, Key (Primary), auto_increment soloid bandid BANDS bandid - bigint(20), unsigned, Key (Primary), auto_increment bandname – varchar(255) placeformed – varchar(255) dateformed – date biography - longtext interview – longtext website - varchar (255) artistimage - varchar(255) SOLOS soloid - bigint(20), unsigned, Key (Primary), auto_increment soloname - varchar(255) dateborn - date biography - longtext interview – longtext website - varchar (255) artistimage - varchar(255) then what do I do? Can you modify this one. I'm going crazy! THANK YOU!

[!--quoteo(post=335294:date=Jan 10 2006, 09:55 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 09:55 PM) 335294[/snapback][/div][div class=\'quotemain\'][!--quotec--] Why would you replace it with anything? You might have to make it a band_id instead, but otherwise, there's nothing wrong. Can I make it accept both soloids and bandids? I thought it would have to be one or the other. Thanks!

[!--quoteo(post=335280:date=Jan 10 2006, 08:41 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 08:41 PM) 335280[/snapback][/div][div class=\'quotemain\'][!--quotec--] I don't understand the question. If I have the above tables with the following, what do I replace artistid with? RELEASES ?releaseid - bigint(20), unsigned, Key (Primary), auto_increment ?reltitle - varchar(255) ?format - enum ?label - varchar (255) ?datereleased - date ?credits - text ?description - text ?artwork – varchar (255) ?artistid - bigint(20), unsigned, Key (MUL)

Right, so if I have a ARTISTMAP for my 1 to many relationship then how do I join in my releases which was usually done by just artistid? Sorry!!! ARTISTMAP artistmap- bigint(20), unsigned, Key (Primary), auto_increment soloid bandid BANDS bandid - bigint(20), unsigned, Key (Primary), auto_increment bandname – varchar(255) placeformed – varchar(255) dateformed – date biography - longtext interview – longtext artistid - bigint(20), unsigned, Key (Primary), auto_increment SOLOS soloid - bigint(20), unsigned, Key (Primary), auto_increment soloname - varchar(255) dateborn - date biography - longtext interview – longtext website - varchar (255) artistimage - varchar(255)

[!--quoteo(post=335080:date=Jan 10 2006, 06:53 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 06:53 AM) 335080[/snapback][/div][div class=\'quotemain\'][!--quotec--] First, you have it somewhat backwards -- the bandid should be in the artists table, not vice versa. Second, if you want a one-to-many relationship (i.e. multiple bands per artist), then instead of having the bandid in the artists table, you'll need a make a new table with the uids from both the relevant tables. Third, IMHO, there are still too many TEXT fields. What if the artist isn't a band but a solo artist? That's why I did it that way. Sorry, I'm confused! I've also taken out the TEXTs for artist/band/solo names and repleaced them with VARCHAR(255). I've left longtext for biographies and interviews, and text for album credits and descriptions. Sound ok? Again, thank you.

[!--quoteo(post=334975:date=Jan 9 2006, 11:34 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 11:34 PM) 334975[/snapback][/div][div class=\'quotemain\'][!--quotec--] You should definitely have two separate tables, no question about that. Keep the stuff that pertains to each separate, it'll make your life easier in the long run. Ok, so I now have this: ARTISTS artistid - bigint(20), unsigned, Key (Primary), auto_increment biography - longtext interview – longtext website - varchar (255) artistimage - varchar(255) BANDS bandid - bigint(20), unsigned, Key (Primary), auto_increment bandname – text placeformed – varchar(255) dateformed – date artistid - bigint(20), unsigned, Key (Primary), auto_increment SOLOS solosid - bigint(20), unsigned, Key (Primary), auto_increment soloname – text placeborn – varchar(255) dateborn – date artistid - bigint(20), unsigned, Key (Primary), auto_increment RELEASES releaseid - bigint(20), unsigned, Key (Primary), auto_increment reltitle - varchar, length (255) format - enum label - varchar (255) datereleased - date credits - text description - text artwork – varchar (255) artistid - bigint(20), unsigned, Key (MUL) SONGS songid - bigint(20), unsigned, Key (Primary), auto_increment songtitle - varchar (255) trackno - tinyint(4), unsigned composer - varchar (255) artistid - bigint(20), unsigned, Key (MUL) (Take this out as can be found through releaseid?) lyrics - text releaseid - bigint(20), unsigned, KEY (MUL), Default (NULL) I can't see how I would join an artist to a band or even to a number of bands. hmm. Sorry for being rubbish! Didn't mean for this topic to go on for so long!

[!--quoteo(post=334971:date=Jan 9 2006, 10:59 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 10:59 PM) 334971[/snapback][/div][div class=\'quotemain\'][!--quotec--] In that case, I think you still need a bands table, and some of the fields that currently reside in the artists table may need to be moved. I was going to have the artist table have both bands and solo artists within it but add a memberof column. If the memberof column was filled with the band that a solo artist was in then this would be displayed, but this wouldn't allow the artist to be in more than one band. Am I better splitting solo artists and bands into two seperate tables?

Do you have the start date also in your stat table and just join it by the band's id? Or do you have it in the band table? Thanks again!

[!--quoteo(post=334852:date=Jan 9 2006, 04:30 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 04:30 PM) 334852[/snapback][/div][div class=\'quotemain\'][!--quotec--] EDIT: I think I'm confused -- aren't the artists the bands? If so, where are the "members"? There will be bands and solo artists. Although a member in a band could also be a solo artist. As well as playing in more than one band. That any clearer?! Hope so!

Ok, sorry! One more question! I'm wanting to achieve the following scenario: If the user selects an band on the site then all columns will be pulled out of my artist table. However, I'm also wanting the user to see individual members of this band on the same page. Should I just add a 'memberof' column (artistid) to my artist table so that the if the artist in question is a band then the memberof column is searched for the artistid of the original band? Let me know if I'm not being clear enough. Thanks!

Great. Thanks again for all your help!

[!--quoteo(post=334645:date=Jan 9 2006, 12:42 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 12:42 AM) 334645[/snapback][/div][div class=\'quotemain\'][!--quotec--] A total of 255 tracks seems reasonable to me, but if you're worried, go to MEDIUMINT. Is that what tinyint (4) is giving me, a max of 255 tracks? Also, sorry to be annoying but I'm having difficulty with figuring out index stuff. Am I adding an additional table for indices which hold the most unique values of the table as well as a few other columns of the indexed table? Sorry if this is a really silly question, but will my database benefit from indices? Thank you!!!!

Great - varchar(255) for all my varchars! Last question! Is my tinyint(4) unsigned ok for trackno? can I go even smaller? I'm assuming that no-one will have more than say 200 tracks on a release (like if it was a box set or something). Your help is so appreciated. Thanks again!

Another version, can't thank you enough! ARTISTS artistid - bigint(20), unsigned, Key (Primary), auto_increment artistname - text origin - varchar (50) dateformed - date biography - longtext website - varchar (60) interview - longtext artistimage - varchar(40) artisttype - enum RELEASES releaseid - bigint(20), unsigned, Key (Primary), auto_increment reltitle - varchar, length (60) format - enum label - varchar (50) datereleased - date credits - text description - text artwork - text artistid - bigint(20), unsigned, Key (MUL) SONGS songid - bigint(20), unsigned, Key (Primary), auto_increment songtitle - varchar (80) trackno - tinyint(4), unsigned composer - varchar (50) artistid - bigint(20), unsigned, Key (MUL) (Take this out as can be found through releaseid?) lyrics - text releaseid - bigint(20), unsigned, KEY (MUL), Default (NULL) I've also replaced performer with artistid, although does this need to be here at all if it can be joined through releseid? Will look up indices! Thanks again!