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

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.