12

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?

5
  • Why are you keeping things in synch and not in sync? Synch (say it) is weird!
    – MrFox
    Nov 28, 2012 at 19:47
  • 1
    @suslik They are both pronounced the same. Synch and Sync.
    – MPelletier
    Nov 28, 2012 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. Nov 29, 2012 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, 2012 at 5:34
  • @PeterSmith The database can't enforce constraints if they are only in your Java enum and not in a table. You're going to constrain values in your database, aren't you? Jul 26, 2016 at 3:34

4 Answers 4

5

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.

4
  • 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, 2012 at 20:00
  • 2
    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, 2012 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, 2012 at 21:32
  • 3
    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, 2012 at 21:34
8

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.

3

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.

2

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.

2
  • 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, 2013 at 14:32
  • In past, I have done the opposite. Whenever there is code change in Enum, on application boot, it syncs those values to Database. Here code is the source of truth. Database is a representation of it. Typically I ignore values in DB which the code can't comprehend but this way, I do get a mechanism to automatically clean up DB if required.
    – Anshul
    Feb 18, 2019 at 9:23

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.