Enforce correspondence between enum and lookup table

Question

I have seen a common design pattern where there will be a "lookup table" in the database like this:

Color

ColorId	ColorName
1	Blue
2	Red
3	Green

with a foreign key constraint on other tables, such as:

Item

ItemId	ColorId
101	2

(Item.ColorId is constrained to Color.ColorId)

In the code, there is a corresponding enumerated type, such as:

enum Color {
    Blue  = 1,
    Red   = 2,
    Green = 3
}

The problem is that the foreign key constraint only protects against one kind of error. You won't be able to insert 4 as a ColorId into the Item table. But this does not protect against an error like:

having:

enum Color {
    Blue  = 1,
    Green = 2,
    Red = 3
}

where the enum does not correspond to the lookup table. So if you have business logic code like:

if (item.Color == Color.Green) ...

it could really be acting on Red, not Green items.

Is there any technique to protect against this type of error? I just encountered an error like that in my current work.

Answer 1

You can probably utilize reflection or code generation techniques to achieve something, but it's not clear whether the potential protection against this rare kind of error is worth the effort of building such a solution and keeping it in good working condition.

You would need to either generate code from the database (which requires a working connection to an initialized database at coding or build time) or check the correspondence of your enum and the database table each time the application starts up and connects to the database.

Both is unsightly and brittle. One variant which is probably a bit less cumbersome would be to check the correspondence as part of automated integration tests, but that still may cause more issues than prevent them.

Answer 2

While not exactly the same problem, there is a similar problem when you serialize enums to files or for sending them over a network.

E.g. you have enums like this

enum Color {
    Red   = 1,
    Green = 2,
    Blue  = 3
}

And you write them to a file and later on read them again from a file, however the code could have been changed in between and now either the order was swapped:

enum Color {
    Blue  = 1,
    Red   = 2,
    Green = 3
}

Or you sent them over the network and on the other side of the network a different implementation is being used and it looks like this:

enum Color {
    Red    = 1,
    Yellow = 2,
    Green  = 3,
    Yellow = 4
}

And my solution to this problem is to never serialize enums to numbers but always to (usually short) strings. And when reading data back, there is a mapping table for mapping strings to enum values again.

That way it won't matter which numeric values the enums have or in what order they were defined, as long as the string table maps "RED" to Color.Red everyone is happy. And it will be quite obvious that something is wrong if "RED" is mapped to Color.Green, won't it? However, who can tell what color "2" is without first checking back with the database?

And keep in mind: A four character ASCII string is as easy to process for a computer as a 32 bit integer as you can in fact store a four character ASCII string as 32 bit integer if you like; every letter needs a byte and a 32 bit integer has four bytes. And the overhead to map enums in and out is tiny enough to be neglected. Also when stored as a CHAR(4) in database, it won't take up more space than a 32 bit integer and databases can handle that type very efficiently during match operations.

Answer 3

Good start

Your lookup design pattern is a good start. It gives you full flexibility to enrich the list of values according to the needs. It facilitates moreover localization.

Away from hard-coded enums

Next step: get completely rid of the enum! If your application deals with colors and you have a lookup table, why bothering to replicate a hard-coded version of this encoding? D

• Define a plain Color type and define its features as much as possible in the database, for a maximal flexibility.
• Initialize the Color behaviors at startup to have in-memory performance as with the enums.
• Ensure constraint of Item.ColorId by verifying any input against the lookup table.
• If some value specific behaviors are required, configure it with some table in the database instead of hard-coding it.

ColorId	InternalName	LocalizedName	IsWarm
1	BLUE	Blau	N
2	RED	Rot	Y
3	GREEN	Grün	N
4	USERDEFINED	Gelb	Y

But if you have no choice...

But if your design or specific problem would nevertheless require a hard-coded enum, then the best would be to make some stat-up self-test during the initialization of your app, to ensure the database values are in sync with the internal meaning.

You could for example query the lookup table for each of the enum, to map it with hard coded values:

// Naive pseudo-code
MyColor test = ColorRepository.getByName("RED"); 
if (test.colorId != Color.Red) {
    throw InconsistentLookupTable ("COLOR", "RED");  
}  

Remark: Every time your app connects at runtime to a new production database, you need to check. It's not just some unit test during the development cycle.