Page 1 of 2
Lyrics database design
Posted: Sat Sep 24, 2005 9:20 pm
by vchris
I've been wanting to create a lyrics website for a long time but I'm just not sure how to design the db. Does anyone know how I can design the db for a lyrics website and how it would work?
Posted: Sat Sep 24, 2005 9:47 pm
by Skara
int `id` (auto_increment)
varchar `artist`
varchar `title`
int `date` --when the song came out. use a unix timestamp.
text `lyrics` --maybe use mediumtext or longtext...
There's a start. It all depends on how much information you want to store. You could let people rate the song, you could keep track of how many times someone looks at the page, whatever.
Try to plan everything out ahead, but remember if it's not too complicated, you can always go back and add another row or two.

Posted: Sat Sep 24, 2005 9:55 pm
by vchris
Wouldn't it be better to not repeat the same artist for each song. I thought about creating an artist table and joining it to the other table but maybe the way you said is the simplest.
Posted: Sat Sep 24, 2005 10:02 pm
by feyd
a typicaly implementation by myself would invole a table for all artists, all albums, all songs (album id (if any), title, release information, license (potentially)), all lyrics, all genres, credit type definitions, all credits, etc; many-to-many tables for artists-albums, albums-songs, songs-genre, songs-credits; many-to-one between credits-credit types; and one-to-one between lyrics and songs.
Posted: Sat Sep 24, 2005 10:07 pm
by vchris
Right, but then it becomes really complicated.
Lets say I have my site up and running and the album names, artists names are all repeated for each song. Would that slow down the database?
Posted: Sat Sep 24, 2005 10:12 pm
by josh
I would definitely use foreign keys, I have built a guitar tabs site with just one big table and it took like 4.5 seconds to list songs for each artist, when I used relational tables it took under .5 seconds.
Posted: Sat Sep 24, 2005 10:17 pm
by vchris
jshpro2, how many records do you have?
Does it become complicated with relational tables?
Can you give me an idea how you did that? I mean like your tables.
Posted: Sun Sep 25, 2005 12:00 am
by josh
like 70,000 records...
First of all analyze your data
I had artist, album, track
So I created those tables
Artist has the fields:
the table album has:
the tracks table has:
I can list albums for an artist by selecting the id from the artist table, then selecting all rows from the album table where artist_id = that id i just selected.
If I need to list all songs in a given album, I first select the id from the album table, then using that id I go
Code: Select all
SELECT * FROM `tracks` WHERE `album_id` = `$id`
where $id is the id i got from the album table
The disadvantages of using this method is "orphaned rows", if you loose a row in the album table, all the rows in the track table that correspond to that album now do not belong to anything, so to delete an album you first delete all the tracks, then the album itself. You can either use transactions or build your own workaround to solve this issue though
designing your database this way might seem "messy", but its significantly faster among other advantages.
Posted: Sun Sep 25, 2005 12:11 am
by timvw
jshpro2 wrote:
The disadvantages of using this method is "orphaned rows", if you loose a row in the album table, all the rows in the track table that correspond to that album now do not belong to anything, so to delete an album you first delete all the tracks, then the album itself. You can either use transactions or build your own workaround to solve this issue though.
Might want to read:
http://dev.mysql.com/doc/mysql/en/innod ... aints.html 
Posted: Sun Sep 25, 2005 5:06 pm
by vchris
jshpro2, thanx buddy that helps a lot!
That's exactly what I was thinking of doing. So basically the field "id" is in relation with all tables right?
When I want to delete a certain album, I just delete the songs from that album and then the ablum itself. If I want to delete an artist I delete the songs, album and then the artist right?
Posted: Sun Sep 25, 2005 5:22 pm
by vchris
hey jshpro2 do you have that lyrics website online? I'd like to see it.
Posted: Sun Sep 25, 2005 6:33 pm
by josh
That only works with InnoDB, but I had never read that.. I might consider switching to InnoDB at some point.
And here is the link:
http://only-guitar.com/
Posted: Sun Sep 25, 2005 6:56 pm
by vchris
Nice site jshpro2.
that's pretty much what I'm looking to do.
By the way do you make a lot of money with those google ads? How many traffic you got per month?
Posted: Sun Sep 25, 2005 8:02 pm
by josh
With all of my web sites combined I make a nice passive income, but I still have to freelance on the side.
Posted: Sun Sep 25, 2005 8:24 pm
by vchris
I've heard some lyrics had copyrights laws and you can't post them on your site, you know anything about that?