This topic has been discussed at length over the last fifteen years, under the subject "One True Lookup Table" (abbreviated OTLT). The advantages of such an approach leap out to the database newbie. The drawbacks emerge over time. See these links for OTLT drawbacks:
Or search for OTLT to find more discussions.
If you create many lookup tables, and many maintenance screens for them, you can create a view that simulates the OTLT by creating a gigantic UNION that includes every code, every description, and the name of the table where the code-description pair is stored.
It's possible to generate such a union using semiautomatic methods, if you know what you're doing. I would imagine that semiautomatic methods would enable you to build a single maintenance screen for hundreds of lookup tables, and then put some logic between that screen and the tables that would insert a new code in the correct table.
As to letting the users introduce new code TYPES, and not just new code VALUES, that opens a whole big can of worms. See the above article discussing EAV. This is very seductive, because it allows the users to design their own underlying data structure. If you disregard performance, this works pretty well for a while. You get a perfectly general database without having to learn the data structure from the users or the subject matter experts.
When it runs into real grief is when you try to use the data as if it were an integrated database, and not just a hodge podge of disjointed opinions about the data. At this point, you're into some serious data archaeology, when your customers expect routine report generation. Good luck.
(Editted to change "data mining" to "data archaeology")
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
