aryaxt aryaxt - 3 months ago 16
SQL Question

Relational database design question - Surrogate-key or Natural-key?

Which one is the best practice and Why?

a) Type Table, Surrogate/Artificial Key



Foreign key is from
user.type
to
type.id
:
alt text

b) Type Table, Natural Key



Foreign key is from
user.type
to
type.typeName
:
alt text

Answer

I believe that in practice, using a natural key is rarely the best option. I would probably go for the surrogate key approach as in your first example.

The following are the main disadvantages of the natural key approach:

  • You might have an incorrect type name, or you may simply want to rename the type. To edit it, you would have to update all the tables that would be using it as a foreign key.

  • An index on an int field will be much more compact than one on a varchar field.

  • In some cases, it might be difficult to have a unique natural key, and this is necessary since it will be used as a primary key. This might not apply in your case.