Natural vs surrogate keys on support tables

Question

I have read many articles about the battle between natural versus surrogate primary keys. I agree in the use of surrogate keys to identify records of tables whose contents are created by the user.

But in the case of supporting tables what should I use?

For example, in a hypothetical table "orderStates". The valuse in this table are not editable (the user can't insert, modify or delete this values).

If you use a natural key would have the following data:

TABLE ORDERSTATES
{ID: "NEW", NAME: "New"}
{ID: "MANAGEMENT" NAME: "Management"}
{ID: "SHIPPED" NAME: "Shipped"}

If I use a surrogate key would have the following data:

TABLE ORDERSTATES
{ID: 1 CODE: "NEW", NAME: "New"}
{ID: 2 CODE: "MANAGEMENT" NAME: "Management"}
{ID: 3 CODE: "SHIPPED" NAME: "Shipped"}

Now let's take an example: a user enters a new order.

In the case in which use natural keys, in the code I can write this:

newOrder.StateOrderId = "NEW";

With the surrogate keys instead every time I have an additional step.

stateOrderId_NEW = .... I retrieve the id corresponding to the recod code "NEW"

newOrder.StateOrderId = stateOrderId_NEW;

The same will happen every time I have to move the order in a new status.

So, in this case, what are the reason to chose one key type vs the other one?

Answer 1

The answer is: it depends.

In your example of changing the order state inside your code, ask yourself how likely it is that you would create constants for those states (to avoid making typos for instance). If so, both will accomplish the same.

In the case that a new order state gets submitted via a form, you would build the drop down (for example) of possible values using either the natural or surrogate key, no difference there.

There's a difference when you're doing a query on the order table and wish to print the state for each order. Having a natural key would avoid the need to make another join, which helps (albeit a little).

In terms of storage and query performance, the surrogate key is respectively smaller and faster (depending on the table size) in most cases.

But having said all that, it just takes careful consideration. Personally I feel that surrogate keys have become something like a dogma; many developers will use them in all their tables and modeling software will automatically add them upon table creation. Therefore you might get mixed reactions about your choice, but there's no hard rule forbidding you to use them; choose wisely :)