Jump to content


Photo

Music Database design help!!


  • Please log in to reply
34 replies to this topic

#21 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 10 January 2006 - 12:17 AM

[!--quoteo(post=334975:date=Jan 9 2006, 11:34 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 11:34 PM) View Post[/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.
[/quote]

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!

#22 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 January 2006 - 06:53 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#23 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 10 January 2006 - 12:17 PM

[!--quoteo(post=335080:date=Jan 10 2006, 06:53 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 06:53 AM) View Post[/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.
[/quote]

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.

#24 cmgmyr

cmgmyr
  • Members
  • PipPipPip
  • Advanced Member
  • 1,278 posts
  • LocationUSA

Posted 10 January 2006 - 01:12 PM

No, the start date is in the band's table there is a join between the two.

#25 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 January 2006 - 05:54 PM

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]What if the artist isn't a band but a solo artist?[/quote]
Then simply don't put in a band_uid -- what's the problem? Alternatively, since a solo artist is still a "one-man band", there's nothing wrong with having a record in both tables.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#26 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 10 January 2006 - 08:32 PM

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)

#27 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 January 2006 - 08:41 PM

I don't understand the question.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#28 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 10 January 2006 - 09:08 PM

[!--quoteo(post=335280:date=Jan 10 2006, 08:41 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 08:41 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I don't understand the question.
[/quote]
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)

#29 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 January 2006 - 09:55 PM

Why would you replace it with anything? You might have to make it a band_id instead, but otherwise, there's nothing wrong.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#30 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 10 January 2006 - 10:45 PM

[!--quoteo(post=335294:date=Jan 10 2006, 09:55 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 09:55 PM) View Post[/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.
[/quote]

Can I make it accept both soloids and bandids? I thought it would have to be one or the other.
Thanks!

#31 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 January 2006 - 11:57 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#32 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 11 January 2006 - 12:11 AM

[!--quoteo(post=335329:date=Jan 10 2006, 11:57 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 11:57 PM) View Post[/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.
[/quote]

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!

#33 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 January 2006 - 01:21 AM

What you should really do at this point is put in some dummy data and see how it all works together, or draw one of those annoying DB design diagrams, if that's your thing. It will be much easier to see it this way. However, to make a _final_ point, you simply have to decide logically whether or not bands -OR- artists have releases, and based on that, put the appropriate FK in the releases table. I think you can take it from here -- especially after 32 posts; good luck.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#34 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 11 January 2006 - 10:58 AM

OK! Thanks for all your help!

#35 franck

franck
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 09 September 2006 - 04:00 PM

Hello ... no one posted in this topic for at least 30 days, I'm also designing a DB for localbands but this DB will only englobe metal bands ... and there are lots of styles inside metal music category.

Anyway an idea for your DB is that you could have the table BANDS and if there is just one member then it is still a band.

Now ... I'm just going to start with this other idea of database i hope you guys are stilling around to give me a hand .. bye




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users