3

Reference Data, or lookup tables are things like CustomerType, ProductType and so on. They change infrequently, occasionally a new type is added or an old one retired. In code they're often reproduced as enums/constants, and also used to populate combo boxes. Adding a new Type shouldn't break existing applications, and more often than not those new types are only required to support a feature of a new application, the legacy app(s) should ignore it.

This situation will be familiar in most dev shops, after a few years/months it's messy, uncontrolled and, if the DB and code gets out of step, bad things happen.

How do others manage this issue? What does the code/DB look like, and how is it versioned?

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
MrTelly
  • 14,657
  • 1
  • 48
  • 81

2 Answers2

2

Do you mean, that IDs and labels are hard-coded in the code, and appear in a look-up table in the database as well?

The approach we've taken is to read the 'type' IDs and labels from the database and use them to populate list boxes.

(Fortunately) I've not had to support different versions of an application that need to read different sets of values from the same look-up table.

I've heard of people assigning minimum version IDs to look-up table values. The application passes in its version (1.5 maybe), and retrieves all look-up values with a version 1.5 or less. Look-up values added for a later version of the application (e.g. 2.1) would be ignored.

This obviously introduces some significant maintenance overhead.

luke_f
  • 595
  • 2
  • 7
  • We use the same approach for List boxes, but there's often code like If Customer.CustomerType = CustomerType.Individual there's the Enum in use – MrTelly Feb 13 '09 at 02:45
1

We created a home-grown tool that runs queries specified in one or more files during the build and generates enumerated type classes, one for each reference data table. At a minimum the tool only requires reference data tables to have two columns: a primary key (with unique constraints) and a string. Each enum instance has a name generated from the string (after getting munged through an algorithm to convert the name into upper case, replace whitespace and other invalid characters to underscores, etc.).

The tool is flexible enough to allow for additional properties on each value; e.g., a "display name", a "description", maybe associated numeric values, and other simple types. We also generate static methods on the enum class to get various subsets of values; there's always at least one that returns all values, but we can have additional ones generated based on SQL queries. For a color enum, we might have a "primaryColors()" static method, for example. Additional static methods are generated to look up a value based on its key; e.g.,

public static Color valueOf(int key);

The enums make it easy and more readable to use well-known reference values in code; e.g.,

if (selectedColor == Colors.RED) {
   .
   .
   .

}

This does have a disadvantage of requiring an additional build step, but in our case that's far outweighed by the advantages: cleaner code, assurance that the UI, business logic, and database valid values are in sync, etc.

We've frequently talked about having a hybrid of a static mechanism as described above plus adding more dynamic behavior, but we've never actually felt it compelling enough to add to the complexity.

Richard Steele
  • 2,227
  • 1
  • 15
  • 14
  • Thanks for sharing your example, we are contemplating a similar strategy. A key attribute would seem to be getting all the reference data elements to work with Intellisense in the IDE. That provides a lot of safety that we are using the correct value and that issues would be caught at compile time (mixed VB6 and C#). – StayOnTarget Jul 20 '16 at 13:55