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 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.