Almost every database enabled project that I’ve written in my career had a requirement for lookup values. Enumerations are great when needed to pass parameters between methods, but when you need to display values to users, I wanted something more flexible. There are several things that usually come up as requirements:
- I want to be able to use a list of values in drop downs
- I want the values to be categorized
- I want the values to be ordered by more than alphabetical
- I want the values to have alternate values that can be used for decisions and code conversions
- I want the values to be dynamic, so I can add and remove them at will
- I want to be able to deactivate values as apposed to deleting them
- I want to do all this without a software release
- I don’t want to deploy values by using Identity Insert into different environments
- I want a user interface to maintain the values
- I want to be able to store different types of values, strings, dates, integers etc.
What typically happens, is a table of values is created, often with a few records that are for a specific purpose. These values are solely maintained by the DBA.
This usually forces a developer to do one of two things:
- Use magic numbers
if (EmployeeTypeId == 1) { }
2. Use textual value
if (EmployeeType == "Supervisor") { }
In example one (1), using magic numbers is problematic. If there is ever a day that users can add values to this table, how do you guarantee that the unique Ids are synchronized between DEV, QA, UAT and Production?
In the second example (2), the business users will inevitably want to change the text value of an entry, thereby breaking code.
As an afterthought, a column will be added later to allow for an active (bit) flag of some sort.
Why should I care?
Not having a centralized lookup system means that the following things will occur:
- Items will be added to this table in DEV and then not propagated uniformly, causing a failed deployment to production. I’ve spent many late nights troubleshooting deployment issues that turned out to be missing lookup values in a table.
- Someone will put in a request to change a value in production and cause exceptions to be raised in the application. The development team is rarely consulted for changes like this.
- The DBA will be strapped with babysitting these tables that are strewn about the database.
- Lack of Foreign Keys will cause developers that are troubleshooting or enhancing to spend lots of time tracking down tables that have lookup values.
- Developers may assume there is no lookup table and hard code the values they see elsewhere in their new pages. Then future developers that find the table, will see that there is a mismatch and have to spend extra time to rectify the issue.
Whenever I see pain points like this, I usually try to think of a solution that could resolve known issues, and as a plus, be generic enough to be re-usable. If there isn’t a coordinated effort, your project could wind up with two or more solutions for providing an engine to accomplish this.
The advantages of a unified method are many:
- Support becomes easier, as all lists of values are handled the same way in code
- All lists of values are stored in the database in the same format
- An admin web page can easily be used to maintain all the lookup lists
- The engine can have special functions built in
- The engine and it’s abilities can be easily versioned
- The engine can be used to convert values
I devised a simple table structure to be used for look-ups. It has been enhanced a little over the years, but has remained essentially unchanged for the past decade or so. That’s right, this simple solution has been able to solve all the lookup needs over the years without modification!
First, let me explain what really adds the power to this structure. There are two columns that are more important than any others:
- lu_categories.catshortname
- lu_items.lucode
When a new [lu_category] record is added, the [catshortname] is a value that is used in any code to pull back the category. So my typical implementation is that once a category is created, the [catshortname] should never change. The ONLY reason to use the [lu_category].[id] should be in the maintenance user interface, but never anywhere else. This solves the problem of synchronizing this table between environments.
Also,When a new [lu_item] record is added, it must Foreign Key (FK) back to the [lu_category].[id] column. The [lu_item].[lucode] is the way that this item is typically referred to in code. The [lu_item].[id] can, and should, be used as a foreign key in tables that use it. This can cause the dreaded issue of Primary Key coordination mentioned earlier if, for instance, other data is being moved from environment to environment that has an FK to this table, however, this issue would be there no matter what your solution, so this is not made better or worse by using this table.
You can use this to lookup values by creating an SQL function, and by using a Lookups business object in your C# application. I’ll be posting some of my Lookup object code in future posts.
SELECT dbo.GetLookupValue("EmployeeTypes", "SUPERVISOR");
string _EstimateTypeId = _lu.GetLuItem("EmployeeTypes", "SUPERVISOR").luvalue;
In the above examples, there are two simple calls to get the luvalue for a record in the lu_items table using the categoryshortname and the lucode. This is a typical use case.
There are many that argue against this type of structure. Since there are two PK’s involved in the relationship makes proper FK’s impossible, so they say. For a typical application, there is only one FK required from the lu_items table. If you’re concerned that this Id might be for a value that is in the wrong category, then I believe your data issues are much more vast than worrying about these two tables. If you live in a world where you often have data corruption issues through mismatched FKs, then don’t use this solution. My only concern is that arguing against it in general is improper. This is a case where it can make so many lives simpler, even if there are edge cases where it is not optimal.
I’ve written a demo application using a .NET Core MVC application that not only shows the lookup system, but demos home grown paging as well. Feel free to download it an give it a try:
YTGI/YTG-MVC-Lookups: Lookups / Paging Demo Site (github.com)
Thanks for reading, I hope this has given you some food for thought about designing engines, or micro services, that can be used throughout your organization to simplify development and support.
