Lookup Tables and Your Data Model - Firebird

We often use lookup tables—also known as control tables or definition tables —to store static rows that can supply expanded text, conversion factors, and the like to output sets and, often, directly to applications as selector lists. Examples are “type” tables that identify entities such as account types or document types, “factor” tables used for currency conversion or tax calculation, and “code lookup” tables storing such items as color-matching codes. Dynamic tables are linked to these static tables by matching a key with the primary key of the static table.

Data modeling tools cannot distinguish a lookup relationship from a master-detail relationship since, simplistically, one lookup row can supply values to many “user” rows. Tools represent it as a parent -child dependency and may erroneously recommend a foreign key on the “child” side. Yet, in an implemented database, this relationship is not master -detail or parent-child, because the primary key value of the lookup set commands one and only one column. It has no effect on other relationships that this “pseudo-child” participates in.

It is tempting to apply a formal foreign key constraint to columns that reference lookup tables, with the argument that a cascading referential constraint will protect data consistency. The flaw here is that properly designed lookup tables will never change their keys, so there is no potential inconsistency to protect against.

Take a look at this example of a lookup relationship, inherited by converting a very poorly designed Access camping goods application to Firebird. Access client applications can do cute things with entire tables that allow amateurs to build RAD applications. This table was used in a visual control that could display a table and “transplant” a value into another table at the click of a button.

Here is a DDL fragment from one of the tables that used COLORS for a lookup:

There were a lot of problems with this key. First, the COLORS table was available to the inventory buyers to edit as they saw fit. Updates cascaded all over the system whenever new items came into stock. Deletions frequently stripped the color information from the relatively few items where it mattered. Worst of all, the bulk of items in this system were one color, 'NEUTRAL', with the result that the foreign key’s index was a real showstopper on inventory queries.

The “relational way” to avoid the unplanned breakage of consistency would have been to use a lookup key with no meaning as data (i.e., an atomic key):

Such a key need never change and it can (and should) be hidden from users entirely. Tables that use the lookup table store the stable key. Changes in available values are implemented as new lookup rows with new keys. Values already associated with keys do not change—they are preserved to ensure that history data is not compromised by subsequent changes.

In the event that, even with the higher distribution of key values, the foreign key would produce an index that was still poorly selective over a large table, the improvement to the stability of the table justifies avoiding a formal referential constraint. Existence of the referenced primary row can be easily enforced using custom triggers.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics