The Basic Schema
20 May 2011
With some of the tools chosen, it is time to create my database schema, or
something like it. Below is my first guess at what the basic organization should
be. Notice that Songs and Tracks are separate. This reflects the reality of
Songs being intangible objects while Tracks are physical manifestations of
those Songs. Tracks are the shadows on the wall of the cave (thank you,
Plato).
The relationships shown above are oversimplified, despite being about as much information as provided by most other methods for storing this kind of data. Let’s dig deeper into these relationships.
##Song–Creator Relationship
Let’s first define a Creator as a person who is either a composer,
lyricist, or both. The Song_Creator entity links Creators – who are
not limited to being any one Creator_Type – to Songs. Additionally,
Songs often have a primary Creator rather than equal contributers, so we
also want to put this in our representation of that interaction. A final note
on the Song–creator relationship: the diagram appears to show a
many-to-many relationship between Song and Song_Creator when it is really a
one-to-many relationship where Song has many Song_Creators.
##Song–Track Relationship
I’ve already outlined why these exist as separate entities. The relationship here is many-to-many to account for not only the many different recordings of the same song but also to account for medleys.
##Album–Track Relationship
It is already assumed that Albums can have multiple Tracks, but we also
assert that Tracks can be released on multiple albums (ex. singles,
compilations, etc.). Album_Tracks represent the relationship here with the
addition of the track number. Album_Sides are pulled out here for the moment,
but they could easily be rolled into the Album_Track entity as a property.
##Performer–Track Relationship
This is the most intricate real-world relationship we model here. We relate to
Tracks rather than Albums here because of the common cases of compilations
and guest musicians. We model performers as both Musicians and Ensembles.
Each Performance represents an individual contribution to the Track. This
contribution also relates whether the Musician is a primary contributer and
what, if any, Ensemble they are a part of on this Track. An Ensemble is
considered primary if any of the Musicians in the Ensemble are primary.
Further iterations of this model may see the addition of roles to
Performances, which would allow the merger of Musicians and Creators. The
relationship between a Musician and a Track is so close to the relationship
between a Creator and a Song that they could be easily merged.
I have done all I can think to account for edge cases. As I start building out tests and building out the models, I will need more edge cases to test against. Feel free to leave me comments with albums that you think might break my model.