When should lookup values for different fields be combined in to a single lookup table?

As part of a group project at University we've hit a bit of a deadlock with a design decision (read: argument). We have multiple fields, on multiple tables, that are to be used as selections (ie, they'll become some kind of drop-down box, with a limited set of options). At the same time we need to make it possible for the admin users to modify the options that are available for each field from within the system.

Now, if it was a single field in a single table, I would just create a new table containing all the possible lookup values and replace the field in the original table with a lookup value that references the lookup table. And we plan on doing something like this, but, since we've got several fields across several tables the path is less clear.

Currently, we have tw开发者_Go百科o competing design suggestions, which I've outlined below. I've intentionally omitted the arguments behind the two options because I'd like to see what you guys have to say without adding bias.

I'd like to know, at least:

• In which circumstances is each of these options the best?
• Are there any no-so-obvious gotchas with either option that we should be aware of?
• Are these any other options that we have missed?

Seperate:

Give each field in each table a seperate lookup table to reference. Queries simply join each table to its field as required.

Combined:

Combine all the lookup tables in to a single Lookup table. Have a Fields table, which describes to the application which fields are modifiable, and then add an intermediate table to create a many-many between Fields and Lookup to describe which lookup values are available to which fields.

---

As you've identified there are two main choices;

On the one hand you could have many identically structured lookup tables. The disadvantage of this approach is the apparent duplication of an identical table structure. It looks like a form of duplication which you no doubt want to avoid.

On the other hand you could have exactly one lookup table supplying many different types or categories of lookup data. The disadvantage of this approach is that (in an evolving system, which a school project might not be) it is inevitable that eventually one of these categories of lookup will require a special extension attribute. When this happens you will find yourself a bit stuck - you won't want to modify the table for just one type of lookup.

When this happens you'll be faced with a number of options, none particularly pleasant;

• add a nullable column to your central lookup table
• create an "extension" table that extends your central lookup table for this special lookup
• move the special lookup into its own table, and treat it as special from then on

Whenever you have to mess up a clean design to cater for even just one exception, you have a problem.

And years of experience tell me that domain lookups can be among the most perverse and surprising of all entities. It's simply a bad idea to lump them all together into one bucket, no matter how appealing that idea appears at first. They will mutate over time, and you'll end up with so many edge-cases, exceptions, and custom flows of logic you'll be sorry you ever bothered with the abstraction.

So going back to the first design and the "apparent duplication", maybe this isn't such a bad thing after all, it might even be the more robust approach.

---

The "combined" option sounds suspiciously like the OTLT anti-pattern. http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html