Is it a good idea to put different types of lookup values into the same table?

Question

On a blog I read a proposal to replace many tables like this:

lookup_genders
0 | Unknown
1 | Female
2 | Male

lookup_countries
0 | Unknown
1 | Germany
2 | UK
3 | USA

into a single table like this:

lookups
0 | Unknown | Gender
1 | Female  | Gender
2 | Male    | Gender
4 | Germany | Country
5 | UK      | Country
6 | USA     | Country

The reason provided there was to get rid of many almost identical O/R mappings.

Is this a really good idea? Which problems could arise? Under which conditions would this be a good idea?

Answer 1

The first time you want to add a new property to your countries, you are in trouble. For example, what would be the capital of 'Female' ?

Answer 2

We do this, we have a GenericOption table which is used for exactly what you describe. We also let 'Super Users' add new options to certain 'types' in the table in order to cut down on maintenance.

We're using Navision at the back end though so we have to pay for tables!

Answer 3

I don't see why it is a good idea, Bigger tables are slower.

Answer 4

Personally I prefer to have each lookup/enumeration in a separate table, but I've come across projects that prefer to have all lookups in a single table.

The advantage of this approach is that maintenance (in terms of UI programming) is cheaper with a single table, as it can be accomplished by editing the base table.

I recommend creating views for Country, Gender etc. for app logic.

Disadvantage: The concept of a generic table falls apart as soon as you want to store attributes for your lookups. But then the views may help you ease the migration.

Answer 5

It all depends on what your design goals are. I am a fan of designs like the one you described above. You can easily make three tables: types, entities and properties and have support for adding virtually unlimited amounts of different data of different types in a way that supports fast lookup and extensible modification / expansion, or in other words, a system that is highly custimizable.

By making a properties table in which you can assign unlimited number of property->value pairs you solve the issue Zed raised.

Answer 6

It is generally a bad idea. It is superficially attractive, but leads to data disasters.

The main reason for this is that it is hard to write the foreign key constraints that reference the One True Lookup Table (OTLT) correctly. For example, if someone enters the value 3 in a column that is supposed to contain Gender, the DBMS cannot help you diagnose the problem. To do so, you'd have to store the constant string 'Gender' in the referencing table as well as the value 3, and the foreign key constraint would then say "there is no value '3,Gender' in the OTLT so you can't insert the data". But that constant 'Gender' value is a horrible case of repeated values - far more wasteful than having a separate table for Gender codes.

Answer 7

Advantages: You don't have to type as many create statements. You don't have to create multiple data entry screens.

Disadvantages: No place to unambiguously put additional attributes. No clear way to validate that a valid selection from the list is being referenced in any given place. Table size must be increased to accomodate the type code. (And rather than putting the text "Gender" or "Country", you really should create a new table to hold the type values and post the id from that table.) Attempts to create dropdown lists of choices or other similar data entry conveniences have to add the complexity of checking the type.

Conclusion: Bad idea. The advantages are easily achieved in alternative ways. Like, (a) Learn to use cut and paste. (b) Write a generic data entry screen that is called with a parameter.

It is certainly true that there are times when textbook normalization causes more problems than it solves. This isn't one of them.