A name for the DB entity relationship table and is that a good idea?
I am not an expert on DB design and do not suspect that I am a beginner. If he answered better in another forum (or from simple help), please let me know.
Considering the "Records" table and the "Artists" table. Both tables have specific primary keys. There is a relationship that we want to express between these tables. Namely: an artist may have many entries or no entries. A recording can only contain 1 or 0 artists. (We may have some obscure recording without a famous artist).
I thought the solution to this problem was to have a foreign key pointing to the artist in the record table. This field can be null (the entry has no artist). In addition, we must define cascading deletes so that if an artist is deleted, all posts that have a foreign reference to that artist now have a foreign key with a null value. [I really want to keep the actual recording when you delete the artist. Our actual tables are not "artists" and "records" and a record can exist without an artist].
However, this is not how my colleagues ask questions. There is no foreign key column in "Records", but rather an additional table "RecordingArtist_Mapping" with two columns,
RecordKey ArtistKey
If the Artist (or Entry) is deleted, the corresponding entry in this mapping table is deleted. I am not saying this is wrong, just different from what I expected. I have certainly seen such a table when I have many, many relationships, but not the relationship described above.
So my questions are:
- Have you heard of this way of describing relationships?
- Is there a name for this type of table?
- Is this a good way to model relationships or would it be better with the foreign key idea that I explained? What are the advantages / disadvantages of each?
My colleagues pointed out that with the foreign key idea, you can have many zeros in your table of records, and that violates (perhaps in spirit?) One of the five normal forms in relational database theory. I dropped out of my league on this one :) Does she violate one of these forms? Which one of? How? (bonus points for a simple link to the "Five Normal Forms" :)).
Thank you for your help and guidance.
Dave
source to share
Your solution with a foreign key in the notation is absolutely correct from the point of view of normalization theory, it does not violate any essential normal form (the most important is the third normal form and the Boyce-Codd normal form, and neither of them are violated).
Also, the part that is conceptually simpler and safer is more efficient from a practical point of view because it generally reduces the number of joins that must be performed. According to opinions, there are more pros than cons.
source to share
At first glance, it is just an intersection table that allows you to link many-to-many between two other tables.
When you find that you need one of these, it is usually worth thinking about "what this table means" and "have I included all the relevant attributes."
In this case, the spreadsheet tells you that the artist contributed in some way to the recording, and then you might think about "what was the nature of the contribution."
They may have played a specific instrument or instruments. Perhaps they were guides.
Then you might be wondering if people other than artists contributed to the sound engineer? So you might be wondering if the "artist" is a good table at all, because instead you might want a table that represents people in general, and then you can associate any of them with a record. You might even want to record a non-human contribution - the London Symphony Orchestra, for example.
You might even have entities that contribute in multiple ways - guitarist, vocalist, and producer? You might also consider whether the contributions should be rated in such a way that they are listed in the correct order (which could be a contractual issue).
This is exactly how written contributions are invested - here is a list of author codes used in the ONIX metadata schema for books as an illustrative industry example: https://www.medra.org/stdoc/onix-codelist-17.htm
source to share
Yes, this is a viable setting, this is called vertical separation.
Basically, you are moving a field artist
from recording
to another table with a primary key referencing recording
.
The advantage is that you don't have to involve artists in doing your post searches, the disadvantage is that if you have to, if it's a little slower due to the extra connection.
source to share
Have you heard of this way of describing relationships?
Yes, this is many, many relationships. A recording can have more than one artist. An artist can have more than one entry.
Is there a name for this type of table?
I call them join tables .
Is this a good way to model relationships or would it be better with the foreign key idea that I explained? What are the advantages / disadvantages of each?
A join table is required in many ways. When you have a relationship from one to the other, you must use a foreign key across many tables.
Regarding Level 4 and Level 5 Database Normalization, this is A Simple Guide to the Five Normal Forms in Relational Database Theory in a 1982 article on Different Levels.
In fourth normal form, a record type must not contain two or more independent multivalued facts about an object.
The fifth normal form concerns cases where information can be recovered from smaller pieces of information that can be supported with less redundancy.
I remember the first three levels of normalization with this proposal.
I solemnly swear that speakers rely on a key, the whole key, and nothing but the key, so help me Codd.
source to share