Best practices for referencing natural and/or surrogate key values in code

Question

I'm modifying some stored procedures that manage status changes when records are updated.

For example, if I have these two tables

Request(RequestID, StatusID)
Status(StatusID, StatusName)

I'm trying to determine the best to handle calling out the statuses in code.

Do I use StatusID or StatusName?

It's not guaranteed that StatusID will match between environments (DEV, PRE, PROD, etc).

Also, StatusName could be changed and I wouldn't want to have to alter code because I needed to change a StatusName.

I could create a 2nd unique column, which would sort of closely resemble StatusID.

I'd make sure this column was matched between regions, but that doesn't seem that clean either and sort of repetitive.

Can anyone suggest a cleaner, simpler way?

Answer 1

From the information you've given, StatusID may have different values in different databases, presumably because your keys are generated automatically and are not specified by you. If so then obviously it's impossible to use StatusID consistently in your code anyway (without standardizing the values). Therefore the question becomes "is it acceptable/practical/desirable to hard-code StatusName values in my code?"

The obvious answer is yes, what's the alternative? If you have a certain status that represents 'ready' and you want to reference that in code then you must put something in your code that identifies the status unambiguously.

If you add a second key of some kind (as Carlos suggested) you still have the same basic problem that changing a natural key value is changing the identity of the status and therefore changes the meaning of your code. If you change the 'real' natural key (READY) without changing the second key (RDY) then your code will become more confusing and difficult to maintain.

If you do something more complex like extracting 'constants' or 'configuration parameters' into a configuration file or table or even writing a custom preprocessor to insert key values into your scripts at deployment time, you add lots of complexity for very little gain (unless you have other good reasons for doing it). I've seen this approach used, and it was a huge, unmaintainable mess.

In practice, StatusName is most likely to change because a) someone thinks another name would be 'more accurate' or 'look better', or b) you discover that it doesn't correctly represent your requirements. If you're forced to spend time on a) then just change the display name in your front end or reports and leave the database and code alone. If b) comes up then by definition your current data model and code are inaccurate and must be revised and possibly modified anyway. And when b) does happen, it often results in adding a new code, not changing the existing one (e.g. because someone defined a new process step that there is no existing code for).

And if you are open to changing your development and deployment practices there are other ways to look at this issue too, as others have suggested. Can you make your StatusID values the same everywhere? Technically it's possible, so what are the organizational reasons not to? Can you reduce the probability and impact of StatusName changes through change management and code reviews? Can you improve your requirements process to capture certain information more effectively?

Answer 2

If you have statusID values that need special treatment then they should be the same across environments.
Why would you introduce a statusID that needs special treatment in Prod that has not gone thru Pre and Dev?

What I often do is start iden at 100 and use that for generic status that don't need special treatment.

Then DEV owns the space under 100 for special treatment using IDENTITY INSERT ON.

If deploy from DEV to PRE insert any records under 100.

Answer 3

As a rule of thumb when you have id,value tables (Status, Result, Area, etc..) I usually add a third field that its the record's mnemonic value and always use that, neither the name or the id. Now the mnemonic value is like a business key (well, it is a business key) in the sense that its a business value and does not depend on the database (for the id) or the way it displayed (the description) so for example for your status table you may have

StatusID,StatusName,StatusMnemo
1       ,COMPLETED ,COM
2       ,REJETED   ,REJ

and so forth.

And in your queries you always join by statusId but you add a clause to join against the status table by StatusMnemo. This is a value that's independent across environments and remains constant.

Also in inserts, you always use statusid.

Answer 4

Write a user defined function that accepts status name and gives out the status if wherever you are referring the status id

select * from resources where statusid = dbo.getStatusId("COMPLETED");

This would make sure that resolving the status id always happens within the function that you have defined

Answer 5

The difficulty of matching code to data can only partially be handled with a second column. When someone adds an item, what does this mean? If they re-use a known constant, what does it mean if you don't require this column to be unique?

Often times we will have user modifiable lookup tables, but they will have to be associated with a number of other flags indicating how to interpret the status - "IsTreatedAsExpired", "IsTreatedAsActive" or perhaps other tables which hold the statuses which are treated as certain things.

I think you really need to figure out the scope of what you want to allow with this table first. Because if you have a LOT of code references, you would be better off using a natural key which is in sync with your code on all installations. A possibility to handle this is to use negative numbers for unmovable codes (identity insert to add new unmovable codes) and then have your sequence only add positive ones. But again, this doesn't address the semantics of how your program would handle or use the user-entered extensions.

Again, it's hard to say without getting the full scope sorted out here.