Multiple values one field mysql

Question

Basically I have a a mysql database which i want to create containing the following tables:

• artist
• song
• album

A song can belong to only one artist, however, a song could be in Multiple Albums. How would I go about implementing this in my mysql database. I've been stuck for a few days now :(

Answer 1

You're looking for a join table:

albumsong
    album       -> FOREIGN KEY to album
    song        -> FOREIGN KEY to song
    tracknumber

Then join to query:

SELECT song.*, albumsong.tracknumber
FROM albumsong
JOIN song ON song.id=albumsong.song
WHERE albumsong.album=(some album id)
ORDER BY tracknumber;

Since some albums are collaborations, you might also have a many-to-many relationship for artists/albums, too:

artistalbum
    artist      -> FOREIGN KEY to artist
    album       -> FOREIGN KEY to album

Answer 2

You would use an intersection table. For example:

Artists:

• Artist ID (Primary Key)
• Artist Name

Song:

• Song ID (Primary Key)
• Artist ID (Foreign Key)
• Song Name

Album:

• Album ID (Primary Key)
• Album Name

AlbumSongs

• AlbumID (Foreign Key)
• SongID (Foreign Key)