[AccessD] Lookup Fields in Table Design

Comments in-line:

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow
Sent: Friday, March 26, 2004 10:39 AM
To: Access Developers discussion and problem solving
Subject: RE: [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.

(Dev's site says this adds confusion to a user, when looking at a well
designed database structure.  The key is, the users shouldn't really care
how the database is designed.  A lookup field is still storing the RIGHT
data (ID's, in this case), it's just displaying differently, which is a
catch 22.  A user might be confused that what they are seeing isn't really
in the table, but then again, they would probably be just as confused if
they say the actual ID.  Ever use Random ID's?  If they saw Random ID values
in there, it would mean practically nothing, but by seeing what the ID
represents, they at least understand what's in that field).

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.

(This gives the impression that querying doesn't work against a lookup
field. That is absolutely not true.  it just means that you have to give
criteria with the appropriate type.  With a State Abbreviation lookup, where
the field is text, and the lookup just gives a list of what text can be in
there, this argument is null and void, because the field is the same whether
it's lookup or not.  But if you store an ID in the field, and the lookup
shows text, then you just write querries the RIGHT way, and query that field
with the ID.  In fact, if you use a form for query criteria, you put a
combobox, with the bound column being the ID, and the user doesn't know the
difference at all.  As far as a user creating a form with a value list combo
box.....ummmm, if you let your users do that, you have a lot more problems
to deal with, more important ones in fact.)


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

(This is true.  ONLY if the lookup field is indexed though.  You are already
getting a size increase from the index, which speeds things up, so you are
getting a minor increase if it's a lookup too.  Again (from previous post),
I don't consider this bloat, because it's just a part of how JET indexes
stuff.  We don't consider Indexing bloat, do we?)

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).

(Interesting, saw this on the site, haven't looked into it.  This would be
an issue, but so far the only one I would actually count.  (Don't care about
it though, I don't let my users filter my forms, unless it's a rare
occasion).

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.

(Not true.  You would have a query to link the data you want to display
(unless you want to display an ID), and the joined field would be what goes
in the report.  Lookups have no affect on what controls you CAN put into
place.  Only sets what the default is).

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

(Ah, this is where I read it, not the first paragraph.  What's the point?
Users are confused with good relational methodology as it is.  What's their
point?)

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.

(Not sure what they are saying.  I know that JET recognizes them, so JET can
be used to pull the data anywhere you want.)

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).

(This is arguing about complexity.  Yes, you should know what you are doing
before developing something professionally.  But that doesn't mean that if
something should be used CORRECTLY that it should never be used at all.
This paragraph is basically saying 'If you don't setup your Access User
Level security correctly, you could get errors in your Lookups.'  Well, DUH!
You'll get errors all over the place.)

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


So in conclusion, the only real valid point, IMHO, is the error involved
with filtering forms.  Would have to reproduce to determine where the issue
lies with that.
Drew

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com