Jump to content


Photo

Music Database design help!!


  • Please log in to reply
34 replies to this topic

#1 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 06 January 2006 - 04:40 PM

Hello!

I'm really needing some help. I'm currently trying to create a website dedicated to the local music scene in my area. I want to have a database containing as much info as possible about the artist (name, where they're from, a biography, interviews) as well as info about their releases (titles, credits, single or album). I'd also like to have tracklistings from these releases that the user can click on for individual song info and lyrics. So...

I've setup a preliminary database with three tables: artists, releases and songs.

ARTISTS
artistid - smallint, length (6), Key (Primary), Default (NULL)
artistname - varchar (70), Key (Mul?)
origin - varchar (40)
yearformed - year, length (4)
biography - longtext
website - varchar (40)
interview - longtext
artistimage - varchar (40)
artisttype - char (1)

RELEASES
releaseid - smallint, length (6), Key (Primary), Default (NULL)
reltitle - varchar, length (50)
format - varchar, length (20)
label - varchar, length (30)
year - year, length (4)
credits - mediumtext
description - mediumtext
artwork - varchar, length (30)
artistname - varchar, length (70)

SONGS
songid - int, length (11), Key (Primary), Default (NULL)
songtitle - varchar, length (50)
trackno - tinyint, length (4)
composer - varchar (80)
performer - varchar (50)
lyrics - mediumtext
reltitle - varchar (50)

I hope you can see what I'm trying to do. On the website you click on an artist, you get their bio and a discography. You can click on an album in the discography which will show you the tracklisting for that album. You can then click on that song to get lyrics or whatever.

So, by not really knowing what I'm doing, is my design ok? artist and release are joined by artistname and releases and songs are joined by reltitle.

Also, do I have to declare my intended lengths for varchars. Does it improve the efficiency of the DB in anyway?

Thanks so much for your help!!

#2 cmgmyr

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

Posted 06 January 2006 - 04:54 PM

I just looked through this real quick and here are some changes that I think you should make:

1. In Table_Releases replace artistname with artistid (same as in Table_Artists) You can always join the 2 together, this will save you space and also keep everything connected by a number and not text, not that it matters too much but I think it's more reliable.

2. in Table_Songs replace reltitle with releaseid (same as Table_Realeases)...for the same reason as above.

Those are only minor changes for you...but I think that they would help you in the long run. Everything else looks pretty good.

Do you have the domain set up for this already, I would like to watch your progress with this and I am interested in visiting the site when you are done with it.

I have also made a local music site for syracuse,ny at www.syracusebands.net check it out, and if you need any other help, please let me know.

-Chris

#3 fenway

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

Posted 06 January 2006 - 07:09 PM

A few suggestions:

1. As indicated above, you should only use UIDs as foreign keys in tables -- this allows you to change any column in the original table without having to worry about it. Furthermore, ALWAYS make your UIDs "bigint(20) unsigned" -- I know it doesn't seem like a big deal now, but why worry about it later if your DB grows?

2. You might as well use LONGTEXT instead of MEDIUMTEXT -- there's [a href=\"http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html\" target=\"_blank\"]not much of a difference[/a], and you won't ever have to worry about it ever again.

3. You should have some of your tinyint fields as "unsigned", so that you double the max. value you can store (e.g. 255 vs 127).

4. If you're already using VARCHAR, you might as well specify the largest column size (255), because otherwise you're just limiting your flexibility for no reason.

5. "artisttype char(1)" -- this is a bad idea. Use an ENUM field if you want to use "codes" -- and if it can grow beyond 64 options, make it a separate table.

6. You should be storing "year" as a DATE field, as rename the column to "dateformed", for example. What if you ever want to include a complete date?

7. I don't see very much info regarding indices -- this really depends on queries, but an index on column like name make sense, and probably a fulltext index on lryics for sure.

8. Make sure you specify all your columns as NOT NULL explicitly, unless you have a good reason not too.

Hope that makes sense -- feel free to ask for any clarification on these points. Good luck.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 07 January 2006 - 11:19 PM

Thank you both for replying!

It's now looking like this (with most fields not null):

ARTISTS
artistid - bigint(20), unsigned, Key (Primary), Default (NULL), auto_increment
artistname - text
origin - text
dateformed - date
biography - longtext
website - text
interview - longtext
artistimage - text
artisttype - enum

RELEASES
releaseid - bigint(20), unsigned, Key (Primary), Default (NULL), auto_increment
reltitle - text
format - text
label - text
datereleased - date
credits - text
description - text
artwork - text
artistid - bigint(20)

SONGS
songid - bigint(20), unsigned, Key (Primary), Default (NULL), auto_increment
songtitle - text
trackno - tinyint(4), unsigned
composer - text
performer - text
lyrics - text
releaseid - bigint(20)


fenway - my artisttype was going filled with: B for a band, P for a person. How would enum improve this? how would i use it? similar way? I'm also not sure what you mean by indices. How and what should I specify and why? Thanks!!!!

cmgmyr - no domain yet, just messing about on my local machine. thanks for the interest! hope I get my site going as well as yours! how do get the top 10 bands and 5 newest bands? Great feature!

Thanks again to both of you!



#5 fenway

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

Posted 08 January 2006 - 07:01 AM

A few comments -- first, you don't need TEXT columns for everything, just the ones you expect to be _really_ long (like lyrics) but not song_title, which isn't going to be more than 255 characters ever. Second, you missed a few "unsigned" attributes for the foreign keys. Third, you don't need a default NULL for the auto_increment columns; it doesn't even make any sense to have it there. Fourth, for the artisttype, simply declare an enum with ('person','band') as the options -- you don't need to use a "code" just because you want to keep the column tiny. Lastly, an index is used to optimize searching the tables -- there are many places you can read about this.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 08 January 2006 - 04:56 PM

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!

#7 fenway

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

Posted 08 January 2006 - 07:47 PM

Much better... but you forgot about one of my earlier comments:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]4. If you're already using VARCHAR, you might as well specify the largest column size (255), because otherwise you're just limiting your flexibility for no reason.[/quote]
What I meant here was not to pick a bigger column _type_, but rather to use VARCHAR(255) for all of your VARCHAR columns. It looks to me like you've picked arbitrary lengths, and you're just going to run into problems that way. The whole idea of a VARCHAR is that you're saving storage space (i.e. only the size of the actual text in the field), so why not have the max length?

Also, you are correct -- if you can simply look up the artistID from the releaseID, then don't store them both in the songs table.

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

#8 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 08 January 2006 - 11:21 PM

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!

#9 fenway

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

Posted 09 January 2006 - 12:42 AM

A total of 255 tracks seems reasonable to me, but if you're worried, go to MEDIUMINT.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 09 January 2006 - 12:52 AM

[!--quoteo(post=334645:date=Jan 9 2006, 12:42 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 12:42 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
A total of 255 tracks seems reasonable to me, but if you're worried, go to MEDIUMINT.
[/quote]

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!!!!

#11 fenway

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

Posted 09 January 2006 - 02:20 AM

You can look at the various numeric types [a href=\"http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html\" target=\"_blank\"]at this refman page[/a]. If you have to ask that question about indices, then I would refer you to [a href=\"http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html\" target=\"_blank\"]this refman page[/a] as well. There is no such this as an ®DMBS design that does not include indices. To a first approximation, you need to index anything that you're going to lookup.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 09 January 2006 - 02:23 AM

Great. Thanks again for all your help!

#13 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 09 January 2006 - 03:21 PM

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!

#14 fenway

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

Posted 09 January 2006 - 04:30 PM

Sounds like you need a "bands" table -- with the name and other details -- and then include a band_id foreign key column in the artists table. This is, of course, assuming that an artist cannot be a member of two bands; otherwise, you'll need a 3rd lookup table.

EDIT: I think I'm confused -- aren't the artists the bands? If so, where are the "members"?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#15 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 09 January 2006 - 08:13 PM

[!--quoteo(post=334852:date=Jan 9 2006, 04:30 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 04:30 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
EDIT: I think I'm confused -- aren't the artists the bands? If so, where are the "members"?
[/quote]

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!

#16 cmgmyr

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

Posted 09 January 2006 - 09:34 PM

Hey again!

Sorry I didn't reply sooner...I had a pretty busy weekend. I'm glad you liked how I did the top 10 and recent 5.

For the top 10:
I have a stats table in the database. Each band that has made a profile has and entry in the table. Every time someone clicks on their profile their stats are increased by 1. At the end of my query it is "... ORDER BY hits DESC LIMIT 10"

For the 5 newest:
When the bands sign up there is a start date for each entry so my query includes "... ORDER BY start_date DESC LIMIT 5"

Pretty simple stuff, but it looks cool to the viewer.

Let me know if you would like to know anything else.

-Chris

#17 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 09 January 2006 - 10:42 PM

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!

#18 fenway

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

Posted 09 January 2006 - 10:59 PM

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

#19 browno

browno
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 09 January 2006 - 11:10 PM

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

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?

#20 fenway

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

Posted 09 January 2006 - 11:34 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users