comment on

Lately I've been scratching my head over how to most effectively keep constants in code and constants in a database table synchronized. Since I haven't found any relevant discussions via search, I thought I'd share my thinking here and see how other monks might approach this problem.

As a contrived example, imagine we have a database table PAYMENT_TYPES that contains the following:

PAYMENT_TYPE_ID | PAYMENT_TYPE
              1 | VISA
              2 | MASTERCARD
              3 | AMERICAN EXPRESS

And somewhere in our code we have corresponding constants:

  use constant PAYMENT_TYPE_VISA => 1;
  use constant PAYMENT_TYPE_MASTERCARD => 2;
  use constant PAYMENT_TYPE_AMERICAN_EXPRESS => 3;
[download]

or, more likely, the equivalent enum. Since this enumeration lives in two places, how do we ensure that they stay in sync? (Alternately, can we find a way to eliminate one or the other?)

Some approaches that come to mind are:

1. Keep code and data in sync manually.
Seems error-prone, though suitably designed automated testing could probably mitigate many oversights.

2. Eliminate the enumeration from the database and keep it in code only.
Violates the expectation that the database will be self-describing, and compromises the ability to establish a foreign-key constaint for tables that include a PAYMENT_TYPE_ID.

3. Eliminate the enumeration from the code and keep it in the database only.
Breaks down if the code needs to know what the enumeration means so that it can apply different logic to different cases. Possible workarounds below, though.

4. Keep the enumeration in the database and query it on program startup to define appropriate variables.
The enumeration in the code is no longer constant, and there's a potential performance hit if you have lots of enumerations to load and thus lots of queries to do every time you start the program. (Then again, no need to think about optimization prematurely...)

5. Keep the official enumeration in the database; query the database to generate code that contains appropriate constants/enums.
Have to make sure your code generation really occurs when the database changes. Could automate this as part of the build/test process.

6. Don't worry about it!
After all, if you are changing your constants/enums, you're probably changing fundamental functionality of the code (it's not as if adding a row for 4 | DINER'S CLUB somehow magically enables you to handle that case properly without touching code elsewhere).

I think I'm torn between #1 and #5 right now. #1 has the obvious virtue of simplicity, but I worry about maintainability -- my experience is that if it's possible to forgot to do something, someone (myself included) *will* forgot to do it sooner or later. Admittedly, since I'm pretty rigorous about test suites, it wouldn't be too hard to detect this before deployment. #5 seems a little bit more pure, because the enumeration really lives in only one place, but it does rely on code generation being trigged when the database changes (which is probably not a big deal since I do "builds" to my test environment with ANT anyway). Then there's the part of me that's convinced that the reasoning behind #6 makes this a moot point anyway.

Any other monks who have contemplated this issue? I'd be curious to hear what pros and cons you see in the various approaches (or some totally new alternatives)... and how you ultimately resolved it.

$perlmonks{seattlejohn} = 'John Clyman';

In reply to Synchronizing constants/enums in database and code by seattlejohn

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Pathologically Eclectic Rubbish Lister
	PerlMonks