1

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?

1

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.

  • 1
    The only absolute requirement that a primary key has is to be unique. The other (stable,minimal) are nice to have for performance reasons. – ypercubeᵀᴹ Feb 22 at 11:33
  • Also: please post your answer as text, not an image. – ypercubeᵀᴹ Feb 22 at 11:33
  • Thank you. So it seems using surrogate key has many advantages and the disadvantages comparatively is less of a concern? – user1589188 Feb 22 at 12:13
  • @user1589188 Yes! – DBK Feb 22 at 14:15
  • Thanks! I like your detailed comparison, so picked yours (but like the other said, better to have the answer in text here than to use a picture, for the picture host or url could become invalid someday). – user1589188 Feb 24 at 23:04
2

Both the designs are possible, reasonable, and non-redundant (in the sense that no data can be eliminated in one solution without losing information): 1) leaving the PK of Type as the pair (make, name) and use it as foreign key in Model, or introducing a surrogate PK in Type and using it in Model.

And their pro and cons balances: the first option allows to perform more efficiently a join between Model and Make, the second one uses slightly less memory.

  • Thanks. But I mean the data for the makes (e.g. A, B, C) have repeated on all three tables. It seems it makes sense for tables Make and Type for joining. But seems too much for table Model, don't you think? – user1589188 Feb 22 at 12:02
  • 1
    @user1589188, the point is that they are necessary for linking the tables, and this design is correct from the normalization point of view. So, unless you want tables which are not normalized, it is the only way to do. In other words, from tuples in one table you have to refer to tables in other tables. In a programming language pointers are used, in relational model foreign keys. In this sense this a not a real redundancy, not more that using the same pointer in different data structures. – Renzo Feb 22 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.