Best practices when using nomenclatures

Transact-SQL
1

Hello,
It's an issue of design and also on the App side but:
What would you recommend when you need to use a mandatory nomenclature in the database?
I have this "Status" nomenclature: "Active", "Suspended", "Inactive", and so on, provided by a State authority. It is not based on Ids (but I have Id column in my table) and is meant to classify a "Contracts" table.
My question is: what would you use in "Contracts" my Id from the "Status" table or the actual "StatusCode" (varchar) from the same table?
The main issue here is the nomenclature can change from time to time (it may come like "Cancelled", "Active" and so on...) so the Id approach must, at some time, accomodate the new nomenclature.

Thank you,
Daniel

2

You should encode the status. That is, assign a numeric value to represent the status: 1 = Active, 0 = Suspended, etc.. If you stick with positive numbers, a tinyint should do, as I can't imagine that you have more than 250 statuses.

1 Like
3

Thank you, Scott.
Indeed, I should create my own codes and use them instead of the identity ones.

Thanks again,
Daniel