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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.