[AccessD] Lookup Fields in Table Design

Drew, Charlotte, all:

To be entirely fair, I looked again at my test db, and found that a form
based on the reference (non-lookup) table, using a lookup combo, produced
roughly the same results as the form based on the table with lookup fields
defined.

So, I think we are laboring under different ideas of performance. Charlotte
and I are correct in the sense that you carry the table lookup baggage in
every query and form based on that table, whether you want it or not, unless
you take steps to turn it off. This, I think, complicates the
straightforward construction of queries, forms and reports.

Drew is correct that there exists in Access a very complex and undocumented
mechanism that will turn Off and On the table lookup feature, depending on
its deduced context, which produces basically the same timed results in
casual tests. This mechanism goes so far as to extend its reach from back to
frontend. AFAIK, it is unique to Access. I think I have clearly demonstrated
cases, both evidential and anecdotal, that show that this mechanism is not
entirely trustworthy.

I guess I am a reductionist in this regard. Take something, like a table,
and strip it to its bare essentials. After all, parts you don't include will
never fail or need repair. Then, where appropriate, add those parts in their
proper context.

Contrast this to the constructionist approach, which would add everything
you could possibly need or want to the table, then turn around and strip out
those parts where you don't need them, all the while trusting that this
truly black box mechanism will magically provide you with the best
performance.

If you've done EVERYTHING correctly, you will probably get away with table
lookup fields. This includes: using small, indexed lookup tables that don't
grow; and appropriate indexes on all main tables.

But without question, you've introduced needless fragility into your table
design. Later on, if you change an index while tuning your table structures,
you may find that the performance of some unrelated queries goes to crap,
and I bet that the table lookup won't be the first thing that leaps to mind
when trying to find the cause.

Tell me, who in the general database community thinks this is a good idea?
Please, show me the data modeling book that has a step that says "embed
client-side UI elements in the table design".

Looking beyond the uncertainty that table lookup fields introduce to the
reliability of the backend, I honestly can't see how you are going to get
ahead in the long run with it. It fills no functional niche that isn't
covered by more standard, broadly accepted techniques. Finally, it is the
little hassles that this feature consistently gives that have caused me to
largely abandon it.

-Ken