1

I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice.

I have a table that will have around 2000 definite unique values, each value will range from 5 characters to 40 in length.

This seems like a partial choice as a natural key, although the values which are 40 characters in length may cause some performance and storage issues when they are referenced elsewhere.

As the total maximum rows in this table is fixed as 2000 and 35% of these rows contain value length of 25-40 characters(65% have length 6-25), shall I go with a natural key here?

With your experience, what would you do here?

cecilli0n
  • 305
  • 2
  • 4
  • 9

2 Answers2

1

As a general rule every table ought to have a natural key because it usually isn't a good idea to duplicate information in a table. Only add a surrogate key if and when you find there is a reason to do so.

nvogel
  • 3,807
  • 21
  • 25
1

First of all, for 2000 records (as others have stated) everything will work. So, for the OP the natural key will still work.

On the other hand VARCHAR fields for primary keys are (in most of the cases) a bad idea. They are inefficient, hard to index and provide slow performance. In most of the cases a numeric field ( int / bigint) will probably work better.

So, in the argument natural vs surrogate, the correct answer is: It depends. Stick with the natural key if it fits your and your app purposes. If not, use a surrogate.

Alexandros
  • 1,022
  • 4
  • 12
  • 23