Music Database design help!!

Recommended Posts

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

Share this post

Link to post

Share on other sites

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

Share this post

Link to post

Share on other sites

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.

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!

Share this post

Link to post

Share on other sites

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.

Share on other sites

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

Share this post

Link to post

Share on other sites

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).

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

Share this post

Link to post

Share on other sites

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.

Share this post

Link to post

Share on other sites

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!

Share this post

Link to post

Share on other sites

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

Share this post

Link to post

Share on other sites

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"

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?

Share this post

Link to post

Share on other sites

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.

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?