Have Geek Brain

Multiple values ​​one mysql field

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

  • artist
  • song
  • album

A song can only be owned by one artist, but a song can be in multiple albums. How can I implement this in my mysql database. I've been stuck for days :(

+2


a source to share


2 answers


You are looking for join table :

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

Then join the request:



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 collaborators, you can also have many, many artist / album relationships:

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


a source


You must use an intersection table. For instance:

Performers:

  • Artist ID (master key)
  • Artist name

Song:

  • Song ID (Master Key)
  • Executor ID (foreign key)
  • Name of the song


Album:

  • Album ID (Master Key)
  • Album title

AlbumSongs

  • AlbumID (foreign key)
  • SongID (foreign key)
+2


a source






More articles:


All Articles