Re: Are lookup fields truly evil?

On Apr 8, 10:08 am, Tom van Stiphout <no.spam.tom7...@xxxxxxx> wrote:
On Tue, 08 Apr 2008 10:34:43 GMT, lyle fairfield <lylef...@xxxxxxxx>
wrote:

Elaborate. Not tersly.

-Tom.

"Allen Browne" <AllenBro...@xxxxxxxxxxxxxx> wrote in
news:47fb45de$0$13252$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx:

"lyle" <lyle.fairfi...@xxxxxxxxx> wrote in message
news:acd520f3-8e62-415b-b387-5d2b7adf2c5f@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
.
I strongly disagree with you and especially with,
Any non-trivial relational database will
include several lookup tables, with the related foreign keys in your
other
tables.
I would not recommend to my clients the purchase of such a database.

I'm not sure I understand, Lyle.

In the Northwind 2003 sample database, the Product table contains a
CategoryID field, indicating what category each product belongs to.
This field is a foreign key to Categories.CategoryID.

The way I understand the term "lookup", the Categories table is a
lookup table, and Product.CategoryID is a lookup field. Are you
arguing that this is a bad design? Or are you saying that this is not
a lookup field?

Very bad design.

It seems unnecessary to repeat the evils of lookup fields from my own
point of view. They are very well-summed up below (taken form the uri
given by the original poster).

The first Evil springs out when opens the Products Table. One sees, in
the Category Field, "Beverages"; what is actually in the Category
Field is 1. This is misinformation.

* ---------
The Evils of Lookup Fields in Tables

Contributors

Arvin Meyer
Joan Wild
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).
* ------------

BTW, this page, The Evils of Lookup Fields in Tables, is pointed to
from (on the same site http://www.mvps.org/access/) The Ten
Commandments of Access, Commandment #2, "Thou shalt never allow thy
users to see or edit tables directly, but only through forms and thou
shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One."
I think Allen's suggestion that "So the criticism questions whether
the the lookup wizard creates more problems that it solves. It is not
advising against the user of lookup fields in your tables." is not
supported.

.

Relevant Pages

  • Re: Make a field lookup dependent on the value in another field of a record?
    ... i think you're correct in your basic grasp of how to use a lookup table via ... your form is based on tblA. ... > A lookup field in a table, based on another table, while it displays the ... > i'll do a quick setup and email it back so you can see how it's done. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Are lookup fields truly evil?
    ... lookup table, and Product.CategoryID is a lookup field. ... Very bad design. ... The Evils of Lookup Fields in Tables ...
    (comp.databases.ms-access)
  • Re: Are Lookup Fields Really Evil?
    ... Lookup tables, referenced via combo boxes in forms is the ... Microsoft IT Academy Program Mentor ... A Lookup field in a table displays the looked-up value. ... If a combobox based on the lookup is used in a form, and a filter is ...
    (microsoft.public.access.gettingstarted)
  • Re: Confused about how to make subforms visible/invisible
    ... first, if you have a lookup field in a *table*, suggest you get rid of the ... lookup *in the table* - they cause a lot of problems. ... next, to hide/unhide a subform control in a mainform, you need to make sure ... Select Expression Builder ...
    (microsoft.public.access.forms)
  • Re: Are lookup fields truly evil?
    ... with the related foreign keys in your other ... In the Northwind 2003 sample database, the Product table contains a CategoryID field, indicating what category each product belongs to. ... The way I understand the term "lookup", the Categories table is a lookup table, and Product.CategoryID is a lookup field. ...
    (comp.databases.ms-access)