Dependency of program code on specific database values

Question

I have two approaches to solving a problem but I don't know which one is better. I will give a simplified example.

First approach

The database will look like this:

color | -------- FFFFFF | 0000FF | 

The code would have to deal with the specific values and do something like:

If (color == 0000FF) displayBlue() If (color == FFFFFF) displayWhite() 

But for this approach I will always have to update the code and the database if there is new color.

Second approach

The database will provide for more information:

color | Name ----------------- FFFFFF | White 0000FF | Blue 

Then the code would be like:

Foreach c in color display( c.Color, c.Name ) 

With this approach, if there is new color I can just update the database. But I feel like I'm mixing concerns. The UI is driven by database values.

Could you advise me on the pros and cons of both approaches and therefore, which one is recommended?

Answer 1

Refining the question

I assume that the color is only an example. It's not the best one, since this RGB encoding provides for 16 millions of colors, some of them having a name, but most don't. I'll come back to it

The more general issue is about using some magic code that you use in your database, for example that correspond to a numeric value representing an enum.

Hard-coded or data-driven ?

If you put the magic codes in a lookup table, the more flexible approach is the second one. So put every value that would be uniquely associated with your magic code in your the same table. Proceed exactly as you would do it for any entity in your domain model.

This second approach decouples your code from specific database values. So software is easier to maintain and in the same time your software can be configured very flexibly.

Sometimes you cannot escape a coupling like in approach 1.
Typically, it's when you have some behavior associated with the hard-coded value. Behavior can not always be purely data-driven. A robust practice in this case is to nevertheless use a lookup table, but use the hard-coded value in an "internal code" property. This allows to easily have several codes sharing some behavior, and in the same time would limit and clearly show the very special fields that are tightly linked to the internal code.

And the separation of concerns?

You wonder if you would not mix up different concerns. I think you are misled in thinking that the color name (or the name corresponding to your lookup code) is a UI concern that should not be mixed up with model concerns.

This kind of database design is not against separation of concerns. The fact that the name is used in the UI doesn't make it an UI concern: that name is a property hat belongs to the magic code. If you have codes "h","m" and "s", "hour","minutes","seconds" are not UI concerns. It's first of all a description of the unit codes. The fact that this name is used in the UI is "coincidence".

Back to the color issue

Now that we have discussed the general issue, we can rethink the way to handle the color.

An RGB color is not suitable for a lookup in a table, unless you'd put the 16 million values just for the principle. However, some special values have a name.

So you could consider a strategy like that:

1. Lookup if the color code corresponds to a known color name
2. If no value if found, construct the color name based on the color components.

The strategy can be fine-tuned according to the needs. For example, you could use a fuzzy naming, i.e. when within a close range of a named color code you'd use that name.

Answer 2

If you were simply consuming the RGB value, then the first approach has legs, but personally I would opt for the 2nd as this is the standard format used for lookup tables. It makes for cleaner code too.