2

I have an enumeration of ~10 values - they are Char such as A through K. These are all unique. They are used for two purposes - an application is forced to select acceptable values and also there is a many to many relation between product and this enumeration table.

Is it bad practice to set the Primary Key as simply this unique value [A-K]? So basically one column of char(1) as the Primary Key. Or should I use an ID field and then set these values in a column and force that column to unique?

The values in this enumeration will never change. They are fixed by the representation. For example, position 1 can only be position 1, like first base will always be first base and never will it become 3rd base (or at least in the last 150 years it has not - my app might not last that long). So that is why I was thinking to simply do it this way - because it truly is permanent, non-changing.

1
  • 1
    Due to the enumeration values aren't going to change in the future, then I'd use enumeration column as primary key. This would prevent you to lookup the values each time you're making a joining query. However, in case the enumeration values are changeable on the future, then I'd use a surrogate key, thus the values change are totally transparent. I hope this help you! Aug 22, 2018 at 15:37

1 Answer 1

1

If there was any sort of choice to be made by your designers or users in the selection of these values I would go for the surrogate ID approach. What has been decided can be un-decided and the change will be easier with surrogates in place.

Since you are adamant the values are fixed by a Law Of Nature (or, at least, some external authority) I would be OK with using the single-column approach. In these circumstance the chances of change, and the wider impact, mean you'll likely have sufficient notice to effect the application update.

1
  • Yes the values will be permanent and no chance for changing .. I just need the table to ensure that other entries will have these exact values - in the application they will be fixed for input (I know I could have fixed the values there ), but I need the data being placed into various tables as there are intermediate tables and also there might be other systems touching the db and in need of the info on a query. I normally like to use the Surrogate as they make the data more mutable.
    – StixO
    Aug 23, 2018 at 0:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.