Database Design – Handling Multiple Many-to-Many Relations

database-designjoin;many-to-many

I've never come up against a similar situation and so I'm looking to gather some tips on how to accomplish this. Here's the situation:

I'm creating an application to track and manage sheet music and their accompanying information. Books can have many songs (Many-to-many). Where I get tripped up is when we start tracking the artists. Books can have multiple artists, but songs can also have different artists.

For example, a Garth Brooks compilation book (Garth is the artist for the book) has a song that was a duet with Trisha Yearwood. Trisha Yearwood isn't a co-artist for the entire book, but can be for multiple songs IN that book. I would like to relate Books to Artists as well as Songs to Artists so I can search and find all Books and Songs that Garth Brooks has been an artist in.

-------
|Books|
-------
|id   |
|name |
-------

-------
|Songs|
-------
|id   |
|name |
-------

//Join Table for Books and Songs
--------------
|Book_songs  |
--------------
|id          |
|books_id FK |
|songs_id FK |
--------------

---------
|Artists|
---------
|id     |
|name   |
---------

Would this be as simple as a separate join table for both Books and Songs?:

----------------
|Book_artists  |
----------------
|id            |
|books_id   FK |
|artists_id FK |
----------------

----------------
|Song_artists  |
----------------
|id            |
|songs_id   FK |
|artists_id FK |
----------------

Or is there a better way to design these relations?

Thanks for your help in advance!

Best Answer

I think your solution (two join tables) is valid. However, I suspect that you are tracking two different things, and wanted to be sure that you were clear on this in terms of design.

The artist(s) associated with a book do not necessarily have anything to do with the artists associated with a song, at the database level. Logically, the artist associated with a book will (almost) certainly also be associated with every song in the book; but, that's irrelevant in terms of the relationship between the artist and the song or the artist and the book.

Some things not to do (not that you necessarily were going to do any of them, but for the sake of someone else looking at this in the future):

  • Do not use the artists associated with the songs to define the artist for the book. I'm assuming that "artist" = "performer", but let's say it also included the writer of the song. A book of Elton John songs might show that every song not only has Elton John, performing, but Bernie Taupin writing; however, that's not necessarily relevant by default. You need the artist to be associated to the book directly.
  • Do not ignore the association between the book artist and each song. This is self-evident once a song is included in two different books, based on two different artists, but at an early stage, one may think that with the artist defined at the book level, one could just add additional artists at the dong level and let the book-level association cover that artist being associated with all the songs. Again, if the Brooks/Yearwood duet was going into a Brooks book and a Yearwood book, this would become problematic. You need that association at both levels, book and song.
Related Question