Lyrics database design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Lyrics database design

Post 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?
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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. ;)
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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.
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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:

Code: Select all

id, name

the table album has:

Code: Select all

id, name, artist_id

the tracks table has:

Code: Select all

id, name, album_id

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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 :)
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post 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?
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

hey jshpro2 do you have that lyrics website online? I'd like to see it.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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/
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

With all of my web sites combined I make a nice passive income, but I still have to freelance on the side.
vchris
Forum Contributor
Posts: 204
Joined: Tue Aug 30, 2005 7:53 pm
Location: Canada, Quebec

Post by vchris »

I've heard some lyrics had copyrights laws and you can't post them on your site, you know anything about that?
Post Reply