SQL DB Model: Multiple artists on single song

Ishvara 0
Viewed 29

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.
database sql
2 Answers

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

userncjV0

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                    |
+------------------+-------------------------+
Slava Rozhnev0
Related