Best practice for keeping named constants in code and database reference tables in sync?

Question

It's generally best practice to use named constants in place of magic numbers, but it sure is tedious and error prone keeping a database reference table and a file of named constants in sync. Is there some easy way to keep these two sources for this info in sync, or am I overlooking an obvious design enhancement?

In case my question isn't clear, here's an example. I have a reference table with 2 columns:

UserStatus
----------
UserStatusID
UserStatus

So this table associates a UsersStatusID 1 with the UserStatus 'Active'. The User table relies on this table to normalize UserStatuses.

So when querying for a User's status from code, I have two options:

SELECT UserStatusID FROM Users WHERE UserStatus = 1

OR

SELECT UserStatusID FROM Users WHERE UserStatus = ACTIVE_USER_STATUS_ID

Best practice is to use the latter so the meaning of the number 1 is clear. But this means I have to maintain a complete list of valid UserStatusID's in my UserStatus table and in my code. How do I avoid having to keep both the DB reference table and my constants file updated? I'm using a centralized constants file so there's only 1 place I have to update for my code, but is there a way to avoid having to update a constants file at all when a new status in introduced?

I have one idea: Schedule a script to run once a day to populate a constants file via a query of the reference tables in the db. Is this the best solution?

Answer 1

Keep your constants in the database. Query them as needed.

If performance is a problem, then load all of the constants at application startup into an internal data structure, then use that throughout the application.

So your code example would be:

SELECT UserStatusID
FROM Users 
WHERE UserStatus = (
                   SELECT INTEGER_VALUE
                   from DB_CONSTANTS
                   WHERE CONSTANT_ID = 'ACTIVE_USER_STATUS_ID'
                   )

Or, if you use an internal structure, it would be more like this:

SELECT UserStatusID
FROM Users 
WHERE UserStatus = g_Constants.ActiveUserStatusID;