Way around Composite Primary Key requiring Composite Foreign Key

database-designnormalizationprimary-keyschema

Here is a table called Make (where name is the PK)

name|other columns
----------------------
  A | ...
  B | ...
  C | ...

And a table called Type (where make, name together is the PK and make references Make.name as FK)

make|name|other columns
------------------------
  A |SUV | ...
  B |SUV | ...
  B |UTE | ...
  C |UTE | ...

Finally a table called Model (where make, type, name together is the PK and make, type reference Type.make, Type.name as FK)

make|type|name|other columns
-----------------------
  A |SUV |CRV | ...
  A |SUV |HRV | ...
  B |UTE |DMAX| ...

It seems to me the Model.make is redundant in the Model table, but it is necessary only because the Type table uses a composite key make, name.

What is the way to get rid of this redundancy?

I believe I can add an unique id column in the Type table, so that the Model table can reference that as FK. But it seems this extra column is just another form of redundancy, as make, name can uniquely identify every row already?

Best Answer

I did this comparison a while ago when I had the same exact question

I compiled this comparison a while ago when I had a similar question. I hope it helps!

Surrogate key of type BIGINT with no business meaning is the only key that meets all the technical requirements of being a primary key: applicable, unique, stable, and minimal. The composite key is an alternate candidate key. It might offer performance advantages for the reasons listed above but it is definitely unstable.