[AccessD] Lookup Fields in Table Design - Coup de Grace

A little scenario:

At the international company you work for, you have been given two customer
databases to work with. One is for your US data, the other from Mexico.
Inspecting both, you can clearly see that, years ago, the Mexican db was
adapted from the US original.

In each db, you see two tables you're interested in, tblAddresses and
tblStates. While the Mexican tblAddresses has been modified somewhat,
tblStates has remained unchanged, with two fields, StateID and Name, both
text. The US version of tblStates contains Alabama to Wyoming, while the
Mexican one naturally contains Aguascalientes to Zacatecas, with their
abbreviations in the StateID field.

Critically, you also notice that table field lookups were also defined for
the StateID field in each tblAddresses table.

So, you build your frontend and link the US tables. You also link the
Mexican tables, which show up as tblAddresses1 and tblStates1.

Next, you open the linked tblAddresses (US version). The table lookups do
their job, showing 'Alabama' for AL, etc.

Happy, you open the linked tblAddresses1 (Mexican version) ... wait ... all
hell has broken loose! Instead of the Mexican state names, you see only the
StateID codes, along with some US State names ... what the!?!?

After some digging, you find the problem: the table field lookup in the
Mexican version of tblAddresses. While it works correctly in the backend, it
fails in the frontend, because the lookup SQL in the linked tblAddresses1
now refers to the *linked* tblState, which holds the US data, not tblState1,
which holds the correct Mexican data!

This demonstrates that table field lookups, by themselves, can cause
incorrect query results.

But it gets worse. The original programmer that setup the databases didn't
know too much about table design, and did not establish a relationship
between tblAddresses and tblStates, only indexes.

This means that you now have a situation where you can enter incorrect
values into a table from the wrong lookup table! If you were under pressure,
and didn't catch this error, and allowed data entry on a frontend with this
problem, the results would be disastrous.

What has been shown here, and validated in my testing, is that table field
lookups can cause inaccurate results, and an insidious form of data
corruption.

It is important to emphasize that table field lookups are the single point
of failure in this scenario. Removing them from the backend tables solves
the problem, by making you match the correct main/lookup tables in a
frontend query. If you had used any method other than table field lookups
initially, you would have avoided this fiasco completely.

By violating the principle of client/server separation, and embedding
client-side UI elements in data tables, table field lookups are data land
mines, just waiting to bite you at some later date. When you use them, you
are embedding an implicit external context assumption into your table. In
this case, the assumption was 'my external name will always be tblState'.
Anytime this assumption is violated, table field lookups will fail. For
this, and the other reasons that I have outlined, table field lookups are a
BAD IDEA.

Match point: Naysayers. Game: Naysayers.

Hey, Susan ... your marshmallows have grown cold!. Pass 'em over here, I'm
in the mood for s'mores!

-Ken