Lyrics database design
Moderator: General Moderators
Lyrics database design
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?
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.
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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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
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.
First of all analyze your data
I had artist, album, track
So I created those tables
Artist has the fields:
Code: Select all
id, namethe table album has:
Code: Select all
id, name, artist_idthe tracks table has:
Code: Select all
id, name, album_idI 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`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.
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
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?
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?
That only works with InnoDB, but I had never read that.. I might consider switching to InnoDB at some point.timvw wrote:Might want to read: http://dev.mysql.com/doc/mysql/en/innod ... aints.html
And here is the link:
http://only-guitar.com/