Many professionals, both on the pure IT side and on the data side, have tried to reduce the pesky complexity of reference data tables by physically combining them. This typically produces two tables: one with a primary key of the table name, and another with the primary key of table name plus code value. The first table has reference data descriptions and the second table has code descriptions. Hopefully, both tables also have definitions. The hope is always that this reduces the need for many separate reference data tables.
Overall, this design is known as the Table of Tables pattern. Things start to go wrong with it when it is found that not all reference data tables are independent, and actually have relations. A closely related problem is multiple hierarchies within a reference data table. There are other design issues too, but suffice it to say that eventually the level of complexity exceeds that of the situation the design was introduced to solve. However, there maybe a use for this fundamental pattern. When reference data tables change, the changes ought to be logged. There are a number of ways in which reference data tables can change – both at the level of the table and at the level of an individual record. The Table of Tables pattern actually seems to fit this need really well. Perhaps we have judged the Table of Tables too harshly, and there is a valid use of this pattern.