SQL Database Design » 12. Auxiliary Tables

12. Auxiliary Tables

Table of Contents

Auxiliary tables are helper tables that serve special purposes. The techniques introduced in this section address recurring modeling problems. Much of this section was taken from the book Joe Celko's SQL for Smarties: Advanced SQL Programming. I even copied the title of this section from this book, although I selected different topics and changed their order. I start right away by quoting a passage from page 485:

... the idea of building a table (in the nonrelational sense) is still valid. An auxiliary table holds a static or relatively static set of data. The users do not change the data. Updating one of these tables is a job for the DBA or the data repository administrator, if your shop is that sophisticated. One of the problems with even a simple lookup table change was that the existing data often had to be changed to the new encoding scheme, and this required administrative privileges.

The primary key of an auxiliary table is never an identifier; an identifier is unique in the schema and refers to one entity anywhere it appears. Lookup tables work with values and are not entities by definition. Monstrosities like[s] "value_id" are absurd.

The important message here is that lookup tables contain static, predefined values and thus their identifiers are the values themselves (no extra IDs!). Lookup tables do not qualify to represent entities.

I do not know why the latter should be the case nor why it is important to be differentiated. I simply do not agree with Celko. You could as well interpret lookup tables to represent incompletely modeled entity types. Even if the logical model dictates to be complete, in practice, you rarely achieve this feat. For auxiliary tables, whichever implementation you like, it does not matter in practice. Note, that I have dedicated an extra section 13.3 Predefined Values to help on selecting predefined value implementations.

Last updated: 2010-08-04