How is lookup table data tracked and initialized in application design?

Question

Background

In a typical Model-View-Controller application using a database to store the model, the consensus seems to be that lookup tables are a solid design decision for enumerated types. Although some DBMSs support ENUM types--a more straightforward solution--people cite disadvantages like "data not being treated as data" and "expensive member list changes" as reasons to avoid the ENUM alternative.

However, one advantage of the ENUM solution is that the enumerated data is, by definition, declared before the application begins execution.

A simple example would be a dating website with Users and Relationship Statuses. The User table would be filled during normal application execution, but the Relationship Status data should (I believe) be "ready to go" as soon as the application is fired up. Even if statuses can be configured/added/removed during runtime, one would think that the client shouldn't have to manually add the initial enumerated set of relationships every time a new instance of the database is used.

As stated previously, ENUM columns achieve this goal, but I have not found any generic documentation on how lookup tables (the accepted best practice) can behave in the same way.

Question

Lookup tables contain data just like any other rows in a database, but with the caveat that they represent enumerations: constructs that, in program logic, are typically defined at compile-time rather than run-time. How do MVC applications handle the initialization and usage of lookup table data, reconciling it with the design paradigms of a program's function?

Attempted Solutions

To illustrate why the previous paragraphs actually pose a mental roadblock, here are a few alternatives which I have considered (still trying to remain system-agnostic) and why they seem to fall short.

1. The user defines the enumerated data during run-time

   As far as I'm concerned, this isn't a valid solution, because enumerated data often plays a role in application logic. For example, a double-entry bookkeeping system may have credit and debit accounts, and transactions for those accounts have to be compared and reconciled differently depending on what account types are in use. Even if a client is allowed to create other account types, should they be expected to know that credit/debit must be added before the application is even functional?

2. Include data insertion statements in the database schema definition

   This option seems a lot more reasonable, and my gut tells me that this solution is correct, but my inexperience inhibits me from tying together the loose ends. Calling the schema creation script typically occurs in some deployment process before the application is run, but what is the procedure if updates to the lookup table values are necessary? Also, the application as a whole should follow the DRY principle, but (referring to the previous example) if application logic depends on the knowledge of credit/debit account types, how is the code supposed to "know about" that data without a redundant definition in the source?

As an aside, I have only ever worked with two ORMs in my history as a software engineer: SQLAlchemy, and an in-shop tool used by an employer to dynamically generate appropriate code for model classes in Java. In any case, I want this question to be system-agnostic, but perhaps this background can assist readers struggling to understand my mental roadblocks on this issue.

Answer 1

Include data insertion statements in the database schema definition

This is the better approach. My team used to use database enum types, and after running into pain trying to add or change values in the enum type we decided to switch to LUTs. Our approach was to define them in the schema definition, and it's worked very well. Allowing the LUT to change at runtime is not a good idea if it's being modeled by a code enum. We added a check constraint to help prevent erroneous data from getting into the LUT.

what is the procedure if updates to the lookup table values are necessary?

Essentially you update the values in the LUT the same way you would update the schema in your database. We have database migration scripts that are executed by Flyway whenever our application starts.

how is the code supposed to "know about" that data without a redundant definition in the source?

This is a bit trickier but achievable. In my organization we just decided to repeat the definitions in our migrations and in code (our enums weren't very long). To avoid that, you could use code-based database migrations (Flyway supports them too) to update your LUT using values from a code enum. In Java you would use the values() method to access all the members of the enum and insert them into the LUT.

The process would look like this:

migration sql v1: define the LUT schema
migration code v2: drop all LUT rows and insert values from code enum into LUT

If then you change the enum in code and need to update your LUT:

migration code v3: drop all LUT rows and insert values from code enum into LUT

Answer 2

I think the reason for your mental block on this is that lookup tables do not represent enums and should not be used for such.

lets say we have a c# enum in our code, which is really a fancy int underneath. We use it to represent possible states of the Door object

1 = open
2 = closed
3 = ajar

Now we persist the Door object to a database table

id = 1
state = "open"

Job done. when we read data out of the table we can parse the state string back into an enum. If we add more values to the enum they can be serialised to and from strings no problem.

But we decide to add a lookup table mirroring the enum

doorStates
id= 1
name = "open"

door
id = 1
state = 1

We can query the data with a join and parse the state string, or parse the state int from door into an enum. We also save some disc space if we have more doors than states.

But! we gave introduced a dangerous artifact to our system.

If we add a new value to the enum we have to ensure that the lookup table matches

If the id goes our of sync with the enum int value we have to be careful not to use it

We have lost meaning in the door table. state 1 is meaningless without the lookup

There is a tendency to stop referencing the lookup table and equate the id with the state. People will ask for a report of all doors in state 2 even though in our code state Id is never used.

If your code always queries door join doorState and omits the stateId column. Which it will. The only real effect of the change is to save disc space.

If you have a 1 to 1 relationship with a child object, use a lookup table. If you have an enum, store it as a string.