Music Database design help!!

[browno]

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

[cmgmyr]

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

[fenway]

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.

[browno]

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!

 

 

[fenway]

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.

[browno]

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!

[fenway]

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.

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.

[browno]

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!

[fenway]

A total of 255 tracks seems reasonable to me, but if you're worried, go to MEDIUMINT.

[browno]

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

[fenway]

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.

[browno]

Great. Thanks again for all your help!

[browno]

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!

[fenway]

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"?

[browno]

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

[cmgmyr]

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

[browno]

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!

[fenway]

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.

[browno]

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

[fenway]

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.

[browno]

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

[fenway]

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.

[browno]

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

[cmgmyr]

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

[fenway]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]What if the artist isn't a band but a solo artist?

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.