Currently I'm redesigning an existing program which uses a master table which contains multiple values. (C# .net core 3.0 & EF) (One big lookup table)
Much of these values are rarely changing and I would put them in a c# enum.
Some examples: Language, Sex, ReceiptStatus, RiskType, RelationType, SignatureStatus, CommunicationType, PartKind, LegalStatute, ...
The list goes on and on and currently has 143 different categories, each having their own values with 2 translations in it.
My company wants the values to be in the database, so a non programmer can change them when they have to.
However it doesn't feel good at all. I would love to separate the table but creating 143 tables seem a bit of an overkill. If it was only 5-10 lookup tables it would have been fine..
Any advice? Stick to 1 lookup table? Feels wrong to my eyes. Multiple tables?
Convince my company we should just use C# enums which work perfectly fine, ruling out the possibility that a non programmer can edit them?
Based on your inclination to use enums, I'm going to assume that these lookup values do not change often.
Buckle up because a lot of hard-fought knowledge about maintainability is embedded in the analysis below. Let me break down the approaches you are considering:
- Pure enums: This is the least flexible approach because it closes a lot of doors. As you said, changing values requires a developer and a deployment. What's your strategy if you eventually have other tables that need to relate to one of your many, many values? To me this is far too restrictive, especially since with either of the other approaches, you could create a .t4 template that generates
enums based on the data. Then if the data changes, you just
re-generate. I do this a lot.
- One giant lookup table: Not as flexible as it may seem! This trades complexity, single responsibility principal, and referential integrity against repetition/table spam and is probably an expression of the Big Ball of Mud anti-pattern. You could add a column to this table that controls where a given value can be used, and that will allow you to have sane drop down lists, but that isn't as good as referential integrity. If other tables need to relate to a lookup, you have to relate against this entire table, which is much less clear. You will have to be careful to enforce your own layer of referential integrity since the database can't help you. Finally, and this is a big deal, if any if your 143 values has or will ever have extra complexity and could really benefit from an additional column, cognitive load begins to escalate. If five of the 143 need their own columns, you now have to hold all five columns in your mind to understand any one column... That is agony. Here's a thought experiment for you if I'm not getting my point across: why not build your entire project as one giant table?
- 143 tables: The most flexible approach, and all things considered, the easiest to maintain by a massive margin. It does not close any doors; down the road you can still create a UI for editing any value you want. If you want to relate other tables to a lookup value, that relationship will be easy to understand because you can relate to LegalStatus instead of GiantEverythingTable, and enjoy the benefits of referential integrity, never having to worry about corrupting your own data. You can also script table and index creation with something like NimbleText (a great tool and a hidden gem). There will be a huge number of tables, which is itself a minor maintenance problem, but it's one that doesn't actually break anything and doesn't lead to cognitive load. This is an acceptable trade-off. I would go this way and generate enums using t4.
The thing about most software projects of any size is that you may look at my objections and say they don't apply, and you might be right. But if this thing is going to be in active development, you have to ask: are you sure? Do you really know what's going to happen in a year?
When considering trade-offs, I've learned to assign a lot of weight to the most flexible/simple decision. Maintainability problems are what kill software projects. They are the enemy.
Hope that helps!