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".
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?
