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

What have you tried so far? Have you created some tables? What columns did they have? — Marcelo Cantos
– Marcelo Cantos, Commented Apr 28, 2010 at 11:05

bobince · Accepted Answer · 2010-04-28 11:06:20Z

4

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

answered Apr 28, 2010 at 11:06

bobince

538k111 gold badges675 silver badges846 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Palmer · Accepted Answer · 2010-04-28 11:08:10Z

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)

answered Apr 28, 2010 at 11:08

Bob Palmer

4,7722 gold badges30 silver badges31 bronze badges

Collectives™ on Stack Overflow

Multiple values one field mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related