How do I design a database with one-to-one or one-to-many relationships for a column?

Question

I am designing a database to store metadata about music.
For instance, I would store the following data:

Song      : A Year Without Rain
Album     : A Year Without Rain
Artist    : Selena Gomez & the Scene
Composers : Lindy Robbins, Toby Gad

...into these 5 tables:

Songs      : song_ID, songTitle, album_ID, artist_ID, composer_IDs
Albums     : album_ID, albumTitle
Artists    : artist_ID, artistName, performer_IDs
Performers : performer_ID, performerName
Composers  : composer_ID, composerName

The problem is, for a given song (song_ID), there could either be one or more more than composer (composer_IDs) associated with it. The same goes for the number of members (performer_IDs) in a band (artist_ID).

I don't want to use arrays or comma-separated values to store multiple values in these columns. How do I fix this problem?

I already read this answer, but I couldn't figure out how to implement that with this design. Any help would be appreciated.

Your question sounds a lot like [this one](http://stackoverflow.com/questions/19437686/entity-relationship-diagram-entity-which-can-have-multiple-types). — Benny Hill, Oct 18 '13 at 19:37
Yes, that's very similar to this question, and makes things a little more clear. Thanks again for pointing me to it. — Vinayak, Oct 18 '13 at 19:46

score 3 · Accepted Answer · answered Oct 18 '13 at 19:32

3

Create another table called Song_Composer, containing two columns: Song_ID and composer_ID. Then you can associate as many composers as you like with each song by adding rows with the same song ID

answered Oct 18 '13 at 19:32

r3mainer

23,981
3
51
88

1

That's pretty much the Many-to-Many relationships on the question you linked. – Rafael Oct 18 '13 at 19:35
Thanks! That's exactly what I was looking for. Sorry about the stupid question. I guess I wasn't really thinking straight. – Vinayak Oct 18 '13 at 19:41

Sobin Augustine · Answer 2 · 2013-10-18T19:44:27.490

1

Change the table structure and also add two tables SongsComposers & ArtistsPerformers

Songs : song_ID, songTitle, album_ID, artist_ID

Composers : composer_ID, composerName

SongsComposers :song_ID,composer_ID

Artists : artist_ID, artistName

Performers : performer_ID, performerName

ArtistsPerformers :artist_ID,performer_ID

edited Oct 18 '13 at 19:44

answered Oct 18 '13 at 19:39

Sobin Augustine

3,639
2
25
43

That's exactly what @squeamish-ossifrage suggested. Thank you for your input as well. – Vinayak Oct 18 '13 at 19:49

How do I design a database with one-to-one or one-to-many relationships for a column?

2 Answers2