how to improve database design?

Question

I am developing a small music website. I am a front end developer and have not had good amount of knowledge in database. I came up with this database design for the website. Can you suggest to improve it?

Few questions I might have as well is ..

• how to change it to support more than more artist to a song ?
• is it alright to have album in the song table or it MUST be a separate table? I don't think I have anything else about album that I want to store but would it be impractical to have album in the song table?

Thanks a lot.

Answer 1

Of the top of my head, I'd probably go with something like this:

This data model has the following characteristics:

• There is a many-to-many relationship between songs and artists (implemented by a "link" table in the middle: SONG_ARTIST).
• Albums are in the separate table. This is not particularly important if all you want is album name, but I assume you'll want more fields later.
• The SONG_NAME and ALBUM_NAME are not keys in their respective tables - there may be multiple songs (or albums) that share the same name.
• On the other hand, GENRE_NAME is an (alternate) key.
• It is questionable whether ARTIST_NAME should be alternate key. I chose to make it so in my model, but this would require you to "invent" distinguishing names for artists that happen to share the same name in a real life. Perhaps a better way would be to also require the date or place of birth, although this has its potential problems too...
• PLAYLIST_NAME is included in playlist's primary key, so a single user can have multiple playlists. Since PLAYLIST has no "child" relationships, there is no need to introduce surrogate key such as "PLAYLIST_ID".
• Instead of plain PASSWORD, there is PASSWORD_HASH and PASSWORD_SALT (to thwart "rainbow" attacks).
• The naming convention uses singular for entity names, which is more in line with recommendations that can be found here.

Answer 2

For multiple artists on a song, you could have a separate table, where each row has the songID & artistID of an artist on that song, so the ID of a song w/ multiple artists would have a row w/ that song ID for each artist. Typical N-to-N relationship construction.

As for putting album (presumably name) in song table, if you didn't have any other info about an album, then it would look kind of silly in its own table. But as soon as you have any album-specifc info other than name (for example, release-date of an album could differ from release dates of its individual songs; also, a song can appear on more than one album), you'd be in trouble.

Answer 3

If you want to have multiple artists to one song you need another table between artist and song, which contains a song and an artist id.

I would also recommend to make an own table for albums, also if you don't have anything to store in it actually, because it would be very easy to edit the name of the title, and you can do it in one place. And I can imagine that it would be intersting to also store an image to the album ;)

Further it's usual to always use an integer as primary key, although the username is unique.