2

In 1970, Edgar Codd published his landmark paper ‘A Relational Model of Data for Large Shared Data Banks’ where he defined relational databases as well as their normalization to first normal form (1NF) in section 1.4, page 381. It is a procedure for eliminating non-simple domains (domains whose elements are relations) of a collection of relations in order to allow a simple two-dimensional array representation without pointers.

First normal form.

I have applied this normalization procedure to the following variants of collection of relations modeling music recordings.

Variant 1

  • Unnormalized relations (non-simple domain interrelationships: artist > album > song):
artist (artist_id, artist_name, album)
album (album_id, album_title, album_date, song)
song (song_id, song_title)
  • Normalized relations (1NF):
artist (artist_id, artist_name)
album (artist_id, album_id, album_title, album_date)
song (artist_id, album_id, song_id, song_title)

Variant 2

  • Unnormalized relations (non-simple domain interrelationships: artist > song > album):
artist (artist_id, artist_name, song)
song (song_id, song_title, album)
album (album_id, album_title, album_date)
  • Normalized relations (1NF):
artist (artist_id, artist_name)
song (artist_id, song_id, song_title)
album (artist_id, song_id, album_id, album_title, album_date)

Variant 3

  • Unnormalized relations (non-simple domain interrelationships: album > artist > song):
album (album_id, album_title, album_date, artist)
artist (artist_id, artist_name, song)
song (song_id, song_title)
  • Normalized relations (1NF):
album (album_id, album_title, album_date)
artist (album_id, artist_id, artist_name)
song (album_id, artist_id, song_id, song_title)

Variant 4

  • Unnormalized relations (non-simple domain interrelationships: album > song > artist):
album (album_id, album_title, album_date, song)
song (song_id, song_title, artist)
artist (artist_id, artist_name)
  • Normalized relations (1NF):
album (album_id, album_title, album_date)
song (album_id, song_id, song_title)
artist (album_id, song_id, artist_id, artist_name)

Variant 5

  • Unnormalized relations (non-simple domain interrelationships: song > artist > album):
song (song_id, song_title, artist)
artist (artist_id, artist_name, album)
album (album_id, album_title, album_date)
  • Normalized relations (1NF):
song (song_id, song_title)
artist (song_id, artist_id, artist_name)
album (song_id, artist_id, album_id, album_title, album_date)

Variant 6

  • Unnormalized relations (non-simple domain interrelationships: song > album > artist):
song (song_id, song_title, album)
album (album_id, album_title, album_date, artist)
artist (artist_id, artist_name)
  • Normalized relations (1NF):
song (song_id, song_title)
album (song_id, album_id, album_title, album_date)
artist (song_id, album_id, artist_id, artist_name)

Which of these variants of collection of relations makes more sense?

23
  • 4
    It's a more complicated model than either of these options embodies. An artist can appear on many albums and record many songs, either by themselves or in cooperation with other artists. See en.wikipedia.org/wiki/Many-to-many_(data_model) – Robert Harvey Jun 16 '20 at 18:39
  • 1
    The relationship between artists and songs is obviously many-to-many. In such cases, there must be an explicit "mapping" or "junction" table which expresses the various connections between artists and songs. In one-to-many relationships, the connection can be folded into the "many" table itself (because just one scalar value must be stored for each), but in many-to-many relationships it must be a table independent of the two being linked (quite simply, because the cardinality of connections which need to be expressed, exceeds the cardinality of either of the tables involved). – Steve Jun 16 '20 at 18:40
  • @RobertHarvey Thanks for the link. Could you elaborate on how my models prevent artists from appearing on many albums or recording many songs? – Maggyero Jun 16 '20 at 21:30
  • @Steve Are you really sure that my models does not already provide a many-to-many relationship between artists and songs? – Maggyero Jun 16 '20 at 21:39
  • 1
    @Maggyero, you seem to misunderstand. If you have 25 rows in artist (variant 1), then by implication you're going to have 5 separate rows that duplicate Michael Jackson's name (and the same for the other four members). That's (usually) considered to be "denormalised" - instead, you want to store Michael Jackson's name just once (and potentially other information, in an artists table), and associate him to his songs separately (associations which, in my example scenario, number 25 in total). The only way to do that is to have a third table (an artist_credits table, per John Wu's answer). – Steve Jun 17 '20 at 10:38
9

Both need some further normalization work if you want a fully normalized model.

However, it is either missing the notion of M:N relationship (first-class relationships), or it is insufficiently normalized, such that modeling an M:N relationship requires duplication, which makes it denormalized.

To get to further normalized database, we would model both entities and relationships using their own tables.

artist (artist_id, artist_name)
album (album_id, album_title, album_date)
song (song_id, song_title, song_duration)

songs_x_album (song_id, album_id)
artists_x_song (artist_id, song_id)

The first three are simple entity tables, and, the latter two are relationship tables, capturing the relationships without duplicating any other info from albums, artists, or songs.

artists_x_album can be derived from the above using a larger join.

Of course, due to the nature of relational queries, given a relationship table we can traverse an M:N relationship a_x_b bidirectionally from an a to its bs and also from a b to its as.


In some but not all cases relationship tables need their own id; not shown in the above (there are only foreign keys).


There is a lot more to consider, of course. Authoring a song and performing/publishing a song are potentially different, for example. Songs are sometimes released without an album.

6
  • Thanks for this interesting answer. There seems to be several database normalization levels. In the 1970 paper that I quoted, Codd only describes the first normal form (1NF). This is the one I used. He described the second normal form (2NF) and third normal form (3NF) in a 1971 paper. Unfortunately I do not have access to it. Do you? Which normal form does your further normalization belong to? – Maggyero Jun 16 '20 at 22:08
  • Also could you elaborate on why my variant 2 is closer to a normalized data model? – Maggyero Jun 16 '20 at 22:13
  • Good question. On second look I don't believe either is more normalized, so I take that part back. – Erik Eidt Jun 16 '20 at 22:46
  • 1
    @Maggyero, it is a 1NF normalisation (and due to the structure of the unnormalized data, it is trivially also 2NF and 3NF) – Bart van Ingen Schenau Jun 17 '20 at 6:36
  • 1
    @Maggyero, 2NF: All non-key fields depend on the entire key (trivial, as there are no tables with multi-field composite keys and non-key fields); 3NF: All field values depend only on the key, there is no correlation between non-key field values that must be maintained (also trivial in this case). – Bart van Ingen Schenau Jun 17 '20 at 11:04
6

You are missing a couple components in your model that represent the relationships between your artists, songs, and albums. These are sometimes referred to as associative tables.

  1. Albums have numbered tracks, each of which is a song.
  2. Artists can be credited with multiple songs.

So a normalized model might look like the following. Your basic tables:

artist (artist_id, artist_name)
song (song_id, song_name)
album (album_id, album_name)

And your associative tables:

album_track (album_id, song_id, track_number)
artist_credit (artist_id, song_id)

The associative tables embody the many-to-many relationships that others have pointed out.

Note that for some types of music, the above may not be enough. For example, for some kinds of electronic dance music, there is an artist credit for the album but not for the songs (e.g. a DJ who put together the album but did not write any of the songs). For that you might need three associative tables instead of two:

album_track (album_id, song_id, track_number)
song_artist_credit (artist_id, song_id)
album_artist_credit (artist_id, album_id)

And in addition maybe you want to keep track of what role each artist had in a song, e.g. if they sung or wrote or played the bongos. For that you would add a role attribute to the artist credit table for songs and a domain table:

album_track (album_id, song_id, track_number)
song_artist_credit (artist_id, song_id, role_id)
album_artist_credit (artist_id, album_id)
credit_role (role_id, role_name)

These are just the basics. There is a lot more complexity in the music industry, and you would have to decide how much to build into your model. To give you an idea, I would recommend you review the work of someone who has already dealt with this, e.g. take a look at the Spotify object model.

7
  • Thanks for the Spotify link and the insightful remark on my album lacking an artist and my song lacking a track number. Regarding the extra associative relations album_track and artist_credit, I am not sure they are necessary since my normalized relations already have their domains. – Maggyero Jun 17 '20 at 0:17
  • Is the credit_role relation necessary? I would have removed it and replace the attributes (artist_id, song_id, role_id) of the relation song_artist_credit with (artist_id, song_id, role_name). – Maggyero Dec 13 '20 at 21:28
  • 1
    Consider a query where you are obtaining the list of songs where Madonna was the songwriter. How would you do that with role_name? You'd have to make sure that the names are used consistently and spelled the same way. Also, you wouldn't be able to fit as many keys into a data page (since a name is longer than an ID) which would increase total I/O. If you're okay with both of those, then it's not necessary. – John Wu Dec 13 '20 at 22:34
  • Ah okay, in other words your credit_role relation is a kind of enumeration type? – Maggyero Dec 14 '20 at 10:43
  • 1
    Exactly. It is a list of valid values. In SQL, we refer to this as a "Domain table." – John Wu Dec 14 '20 at 15:56

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.