Another Database Design Mistake to Avoid – Too Few Fields in the Primary Key
There were two fields in the primary key. A third field did have a foreign key relationship, but was not part of the PK.
The design would work if there was a 1:1 relationship between the third field, and one of the other two foreign keys. Since this was the usual case, this normally worked.
But when new data came along that did not have a 1:1 relationship, but actually had a 1:M relationship, this became a problem. You couldn’t add another row, because the PK would not allow it. More code, manual intervention, or workarounds were required to make things work.
Add the third field to the primary key so that the row makes sense in all cases. The field was already NOT NULL, so it could be added to the PK easily enough.
There was no need to add yet another table, which was the automatic conclusion that others jumped to.
Of course you would do some analysis first. One note would be that by adding the third field, the maximum possible number of rows in the table would now be multiplied by the cardinality of the third field. Right?