SQL DB Model: Multiple artists on single song-CodePudding

I am wondering about the best way to model this for when there are multiple artists for a given song.

id   song_title           artist
=================================================
1    Battle Sirens        Knife Party, Tom Morello
2    Internet Friends     Knife Party
3    Satisfy              Nero

Where ultimately I want it to work like this:

id   song_title           artist
=================================================
1    Battle Sirens        [1,2]

With an artist table like this:

id   artist_name        
===================
1    Knife Party       
2    Tom Morello
3    Nero

It makes the most sense to me to have each artist field as a string containing an array of artist ids: "[1,2]", but this doesn't seem like the right way to do it in just SQL.

The only other things I can think to do are:

Have a new table for combinations of artists with an arbitrary identifier.
Have an 'assoc' table where there are two records for the same song id when there are two artists behind it.

CodePudding user response：

Creating an association table is the normal way of handling a m:m relationship

CodePudding user response：

The best way is use DB normal form with many-to-many relation. This demand 3 tables: songs, artists and song_artists:

CREATE TABLE songs (
  id int primary key, 
  song_title varchar(64)
);

INSERT INTO songs 
VALUES 
  (1, 'Battle Sirens'), 
  (2, 'Internet Friends'), 
  (3, 'Satisfy');

CREATE TABLE artists (
  id int primary key, 
  artist_name varchar(64)
);

INSERT INTO artists 
VALUES 
  (1, 'Knife Party'), 
  (2, 'Tom Morello'), 
  (3, 'Nero');

CREATE TABLE song_artists (
  song_id int references songs(id), 
  artist_id int references artists(id),
  primary key (song_id, artist_id)
);

INSERT INTO song_artists 
VALUES 
  (1, 1), 
  (1, 2), 
  (2, 2), 
  (3, 3);

SELECT 
  song_title, 
  GROUP_CONCAT(artist_name) AS artists 
FROM 
  songs 
  JOIN song_artists ON song_artists.song_id = songs.id 
  JOIN artists ON artists.id = song_artists.artist_id 
GROUP BY 
  songs.id, 
  song_title;

When you can use next query to fetch related data:

SELECT 
  song_title, 
  GROUP_CONCAT(artist_name) AS artists 
FROM 
  songs 
  JOIN song_artists ON song_artists.song_id = songs.id 
  JOIN artists ON artists.id = song_artists.artist_id 
GROUP BY 
  songs.id, 
  song_title;

online sql editor

Result:

 ================== ========================= 
| song_title       | artists                 |
 ================== ========================= 
| Battle Sirens    | Tom Morello,Knife Party |
 ------------------ ------------------------- 
| Internet Friends | Tom Morello             |
 ------------------ ------------------------- 
| Satisfy          | Nero                    |
 ------------------ -------------------------