2

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!

3

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.
1

Name your relationships and things will become clearer. Use specific, strong words that are common in the problem area. "Garth Brooks ... has a song" is ambiguous and weak. He wrote the song, published the song, owns copyright on the song, recorded the song, has custody of original manuscript of the song: these are strong, meaningful statements about the artist's connection to the song. Each will produce a separate relationship in the data model with its own cardinality and membership.

1

We had something similar but take care, a given song can have different performers in a different book! We had separate tables for song and performance, and artists where linked to both. The 'main' song always has the same writer and performers (original version) but then in different books (albums) you can have that song performed by other performer(s). All depends on how far you need to go ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.