Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm making a program that will post data to a database, and I've run into a pattern that I'm sure is familiar: A short table of most-likely (very strongly likely) fixed values that serve as an enum. So suppose the following table called Status:

  Status
  Id Description
  --------------
   0 Unprocessed
   1 Pending
   2 Processed
   3 Error

In my program I need to determine a status Id for another table, or possibly update a record with a new status Id.

I could hardcode the status Id's in an enum and hope no one ever changes the database. Or I could pre-fetch the values based on the description (thus hardcoding that instead).

What would be the correct approach to keep these two, enum and table, synced?

share|improve this question
    
Why are you keeping things in synch and not in sync? Synch (say it) is weird! –  MrFox Nov 28 '12 at 19:47
    
@suslik They are both pronounced the same. Synch and Sync. –  MPelletier Nov 28 '12 at 21:24
1  
Having an enum and the database table is duplication. Unless the database table is to be used by other applications, I'd forego the table and just use the enum. If the table will be used in multiple apps, load the statuses from the database at runtime instead. –  Peter Smith Nov 29 '12 at 2:45
    
It depends on the context. In this case, being workflow or task status values, I would tend to favor keeping them more dynamic over static, as processes often tend to change; and tend to change out of band for software release schedules. On the other hand, if its a stable service that is unlikely to have new states introduced or existing states removed, I might be more compelled to hard-code the enum/denormalize them (depending on how the records are used later). –  JustinC Nov 29 '12 at 5:34
add comment

4 Answers 4

up vote 2 down vote accepted

I would hard-code the enum within your program, as I suspect these different statuses affect your programs logic. If you have a new status, how is your program supposed to react to this new status?

Because the database is part of your application, I don't think it would make sense to affect one without consulting other, sort of speak.

share|improve this answer
    
It's not so much about new statuses (which would definitely warrant a modification of both database and program), but Id values. –  MPelletier Nov 28 '12 at 20:00
1  
I don't see why you would change the ID value without changing its meaning. These sort of enumerations would not be auto-incremented, and these are really just for readability for people debugging the database, as typically your application will be making queries, and it would already know what the ID for pending would be. Of course having a Status table gives you referential integrity, but that's becides the point I'm trying to make. –  Matthew Nov 28 '12 at 21:30
    
Well yes, that's the idea. But if I set the ID's to one place an to others, I'm working off the assumption that they are both right. It's a loose link, no? –  MPelletier Nov 28 '12 at 21:32
1  
We make these assumptions all the time, if you think of a table definition, our program assumes the definition is as such when writing a query. The Status table should not be considered dynamic through the runtime of the application, and thus I don't think you should read it as such. –  Matthew Nov 28 '12 at 21:34
add comment

I would normally load this data into a static cache (usually in a HashMap or something like that) when the application starts up. It avoids having to recompile just because the enum has been modified in some way.

share|improve this answer
add comment

Because those are statuses they should be hard-coded into the application to ensure no DB change will corrupt your program (at least not easily). This is important because every status that must be added has to be coded against first and not simply added to the DB.

You should still have those status values written to the DB with their correct descriptions in case you need to pull a report for example.

Usually I have a small code snippet that will connect to the DB and verify that the statuses listed in a specific table all have the same id/name values that I have hard-coded in memory. If they don't match I will abort the execution of the software.

Depending on your needs you might want to implement slightly different behaviors but overall it's a good idea to have those statuses hard-coded anyway.

share|improve this answer
add comment

We have similar issues on my project (legacy code, hooray!) The major problem is that "the enum tables never change" until they do and code breaks. I have two strategies to mitigate that I've been slowly migrating towards.

First, and best, is to eliminate direct references to these values whenever possible. Always ask, "why do I need to directly use the enum value?" In many cases, this is a sign that the code has too many hard coded assumptions or is trying to do too much manipulation on the data. See if you can't make better relationships in the database or more flexible code to handle what is being manipulated.

When that doesn't work, I go to plan B: code generation. Since the tables change infrequently and we publish new builds regularly, a code generator can read the enum tables and write the enum code. This dynamically generated library is then used in the project. If the DB changes, the next build will not compile, which is a lot better than getting mysterious runtime errors.

share|improve this answer
    
How would you eliminate references to an enum? For example you are sorting or searching by a status in this case. Having some magic value in the DB does not sit well with me either. That is what lookup tables are for. –  nportelli Jan 28 '13 at 14:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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