[AccessD] Lookup Fields in Table Design

Here are "The Evils of Lookup Fields in Tables" from Dev's website:

A Lookup field in a table displays the looked-up value. For instance, if a
user opens a table datasheet and sees a column of company names, what is in
the table is, in fact, a numeric CompanyID, and the table is linked with a
select statement to the company table by that ID.

Any query that uses that lookup field to sort by that company name won't
work. Nor will a query that uses a company name in that field as a criteria.
If a user creates a combobox to select the company using a value list, the
data in the table can be over-written.

Another relationship is created which then creates another set of indexes
when a Lookup field is created, thus bloating the database unnecessarily.

If a combobox based on the lookup is used in a form, and a filter is
applied, the persistent filter effect of Access often saves the filter and
the next time the form is opened, there will be a prompt for the value
(which cannot be provided, thus creating an error).

Reports based on the lookup field need a combobox to display the data,
causing them to run more slowly. The underlying recordsource can also be
modified to include the table, however the index, (unless it was set up
within a proper relationship) may not be optimized.

Lookup fields mask what is really happening, and hide good relational
methodology from the user.

The database cannot be properly upsized to, or queried by, another engine
(without removing all the lookup fields) because no other engines use or
understand them.

If security is implemented, permissions to tables is usually denied, and
RWOP queries are used for data access. There will often be errors that there
are no permissions on a specific table that isn't even being used in a query
(because the lookup field is). If the queries are nested or complex, it can
take some time to track down the lookup that's causing the error (that is,
if it occurs to you).

http://www.mvps.org/access/tencommandments.htm