Name for DB Entity Relationship Table and is it a good idea?

Question

I'm not a DB design expert and have what I suspect is a newbie question. If it's better answered in another forum (or from a simple reference), please let me know.

Given a Table "Recordings" and a table "Artists". Both tables have primary keys suitably defined. There is a relationship that we want to express between these tables. Namely, An artist could have many recordings, or no recordings. A recording can only have 1 or 0 artists. (We could have some obscure recording with no known artist).

I thought the solution to this problem was to have a foreign key pointing to artist in the Recording Table. This field could be null (the recording has no artist). Additionally, we should define cascading deletes, such that if an artist is deleted, all recordings that have a foreign referring to that artist, now have a foreign key of null. [I really do want to leave the actual recording when you delete the artist. Our real tables are not "artists" and "recordings" and a recording can exist without an artist].

However, this is not how my colleagues have set things ups. There is no foreign key column in 'Recordings', but rather an extra table 'RecordingArtist_Mapping' with two columns,

RecordingKey ArtistKey

If an Artist (or Recording) is removed, the corresponding entry in this mapping table is removed. I'm not saying this is wrong, just different to what I expected. I have certainly seen a table like this when one has a many-many relationship, but not the relationship I described above.

So, my questions are:

1. Have you heard of this way of describing the relationship?
2. Is there a name for this type of table?
3. Is this a good way to model the relationship or would be be better off with the foreign key idea I explained? What are the pros/cons of each?

My colleagues pointed out that with the foreign key idea, you could have a lot of nulls in the Recordings Table, and that this violates (perhaps just in spirit?) one of the Five Normal Forms in Relational Database Theory. I'm way out of my league on this one :) Does it violate one of these forms? Which one? How? (bonus points for simple reference to "Five Normal Forms" :) ).

Thank you for your help and guidance.

Dave

Answer 1

On the face of it, this it simply an intersection table that allows a many-to-many relationship between two other tables.

When you find that you need one of these it is generally a good idea to consider "what does this table mean", and "have I included all the relevant attributes".

In this case the table tells you that the artist contributed to the recording in some way, and you might then consider "what was the nature of the contribution".

Possibly that they played a particular instrument, or instruments. Possibly they were a conductor.

You might then consider whether people other than artists made a contribution to the recording -- sound engineer? So that leads you to consider whether "artist" is a good table at all, because you might instead want a table that represents people in general, and then you can relate any of them to a recording. Maybe you even want to record the contribution of a non-person -- the London Symphony Orchestra, for example.

You can even have entities that contribute in multiple ways -- guitarist, vocalist, and producer? You might also consider whether there ought to be a ranking of the contributions so that they are listed in the correct order (which may be a contractual issue).

This is exactly the way that contributions to written works are generally modelled -- here is a list of the contributor codes used in the ONIX metadata schema for books, as an illustrative industry example: https://www.medra.org/stdoc/onix-codelist-17.htm

Answer 2

Have you heard of this way of describing the relationship?

Yes, it's a many to many relationship. A recording can have more than one artist. An artist can have more than one recording.

Is there a name for this type of table?

I call them junction tables.

Is this a good way to model the relationship or would be be better off with the foreign key idea I explained? What are the pros/cons of each?

A junction table is required in a many to many relationship. When you have a one to many relationship, you would use a foreign key in the many table.

As far as 4th level and 5th level database normalization, this A Simple Guide to Five Normal Forms in Relational Database Theory article from 1982 explains the different levels.

Under fourth normal form, a record type should not contain two or more independent multi-valued facts about an entity.

Fifth normal form deals with cases where information can be reconstructed from smaller pieces of information that can be maintained with less redundancy.

I remember the first 3 levels of normalization with this sentence.

I solemnly swear that the columns rely on the key, the whole key, and nothing but the key, so help me Codd.

Answer 3

Your solution with a foreign key in Recording is absolutely correct from the Normalization Theory point of view, it does not violate any significant normal form (the most important one are Third Normal Form, and Boyce-Codd Normal Form, and neither of them is violated).

Moreover, a part being conceptually simpler and safe, from a practical point of view it is more efficient, since it in general reduces the number of joins that must be done. In may opinion, the pros are greater than the cons.

Answer 4

Yes, that's a viable setup, this is called vertical partitioning.

Basically, you move your artist field from recording to another table with the primary key referencing that on recording.

The benefit is you don't necessarily have to retrieve artists with doing lookups on recordings, the drawback is that if you still have to, if would be somewhat slower, because of an extra join.