Database Structure - Comments?
 

I'm creating a lyrics script (as I have posted in another thread), and I was thinking today that my previous database structure wasn't great at all. I rethought it, and think that this new way might be a bit better, but I want to see what you guys think before I go with it.

I have 3 tables, albums, artists, and songs, which could all easily be used together (by JOIN's I believe?), to get the information where it's needed.



So is there any improvements I could make? The reason why I thought separate tables would be better, is to make it easier to make "Browse" pages on the site, and it still wouldn't take away much from getting all the info for a lyrics page.

The structure seems OK, it's just the naming conventions that I would alter. I would go for:

• Albums
• id (pk)
• name

• Artists
• id (pk)
• name (maybe even make this column unique, too)

• Songs
• id (pk)
• album_id (fk)
• artist_id (fk)
• name
• lyrics (consider using full-text search)

You'd set-up 2 relationships: songs.album_id => albums.id and songs.artist_id => albums.id.

Hope this helps!

This point isn't to do with your structure per se, but it's an important aspect of good database design. If you're using MySQL 5 you can setup relationships with cascades so that you can automatically update/delete related records. For example, with cascade deletes you can delete an artist, and MySQL will automatically delete the related records from the songs and albums table.

And offtopic, that can be easily done via SQLYog. :)

I completely like that feature and hopefully is going to make a better use next time.

Thanks Karl.

I'll take a look at that karl.

Also, for the names, I would have done that, I just put the prefix in there to be more clear.

Also, how would you guys recommend implementing an approved field, so neither the artist, album, or song shows up as approved until it is by an admin. I'd like to do it separately, so would just having an approved field in each table work?

In this case it is probably easier to just have an extra field in each table. Then just store a boolean value in the field; 0 being not approved (false), 1 being approved (true).

Otherwise, say for example you created another table and only approve so many songs per album, you would have a row for every approved/not approved song (sure, you could use a delimiter, but too much work). Overall, that would just be too much repeated data IMO.

Yep, I'd have to agree with hostfreak. Boolean in each table is definitely the approach I'd go for.

Okay, I'll do that. :) Thanks for all your help.