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
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
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.
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?
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.
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
debitaccounts, 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
debitmust be added before the application is even functional?
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
debitaccount 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.