One of the projects I work on contains a large database with a lot of lookup fields containing status codes, record types, processing flags, etc. A great deal of these are implemented in a typical normalized fashion with two tables and a foreign key relationship:
Pretty standard stuff, right? Sure, yet at the start of a new development phase last year I decreed Thou Shalt No Longer Do This!
What's the big deal with lookup tables?
On this project (and on many of my others) I had noticed the following patterns:
- The vast majority of the lookup tables contained a single "Name" field containing a human-readable description of that status code or record type.
- Because the database is so large, a typical query might need to do five or six joins just to get the names of the lookup values.
- The values in the lookup table rarely changed. When they did change, it was always as part of a scheduled release.
In short, we were paying a performance penalty on each and every query to obtain unchanging metadata about a small, discrete set of known values.
In addition, dealing with these joins by hand was an annoyance whenever we needed to write manual T-SQL queries or express ad-hoc queries directly against the Linq to Sql data context.
There's Got To Be A Better Way! ™
The solution that we implemented, and that we're still using nearly two years later, is simple:
- All lookup-style data (status codes, record types, etc) have a corresponding C# Enum
- A custom Attribute associates each value with a human-readable string
- A custom Attribute associates each value with a database key representation
- There are no lookup tables or foreign keys.
- The domain model contains properties of the Enum types
- In the database, each lookup field is a string, not an integer foreign key
- When we write to the database, we convert the enum into its database representation and store that value
- When we read from the database, we convert the stored string into an enum instance
- The parsing and conversion is handled via extension methods:
- String.ToEnum<T>
- Enum.ToDescription()
- Enum.ToStringConstant()
A picture is worth a thousand words here:
Was it worth the effort?
After nearly two years of use I'm pleased to say that this pattern has served us well. The extension methods make the lookup values easy to use, avoiding joins improves system performance, and storing strings (rather than foreign key integers) in the tables makes the raw data a little bit easier to use.
Of course, your mileage may vary. This technique isn't appropriate if your lookup values are dynamic (rather than a fixed set) or if you need to track a large amount of metadata in the lookup table. But if your project has the same characteristics that mine does, I recommend you give this a shot.
Happy coding!
Appendix: the source code
I slopped the code for the attribute classes and extension methods onto my Github repo.