CODEWITHSUNDEEP
databasedatabase-design
Gainster
Gainster

Reputation: 5631

surrogate keys as foreign key

Is setting the foreign key as surrogate of the parent table expense ?

Since I have to join that parent /reference table to show the natural key.

But why it is the best to set the foreign key as surrgate for referencing table.

Upvotes: 1

Views: 550

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

It is expensive in a sense that you'd need a JOIN in some situations where it would not be necessary with the natural key (as you already noted). Also, natural keys usually play better with clustering, and are sometimes necessary for correctly modelling diamond-shaped dependencies.

On the other hand, using a "skinnier" surrogate key can save some space and simplify updating (especially when there are several levels of FKs).

Neither approach is absolutely "better" - It's a matter of compromise.

Upvotes: 2

Related Questions