Too Few Fields in the Primary Key


Another Database Design Mistake to Avoid – Too Few Fields in the Primary Key

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.

Too Few Fields In the Primary Key - Corrected

Correction:

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?

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: