[AccessD] Lookup Fields in Table Design

DWUTKA at marlow.com DWUTKA at marlow.com
Fri Mar 26 14:08:33 CST 2004


Gustav, my issue is this, saying that something is bad, and not giving
actual reasons, is pushing your preferences on to people, as fact, instead
of what they are, preferences.

Everyone has had to learn something new at some point in time.  When you
learn something new, it's very handy to listen to people that have been
there, and done that.  It makes the learning curve a lot less steep!  In
fact, IMNSHO, that is the very purpose of this list.  However, when someone
is new to a subject, and you are teaching them your knowledge and
experience, they probably won't have the foundation set, to allow them to
differentiate between what is fact, and what is preference.  That is YOUR
job, as the teacher.  There is nothing wrong with passing along your
preferences, especially if you back up why you do what you do.  However,
saying that Lookups are BAD PRACTICE, is NOT passing on a fact, it's passing
on a preference.  If the statements made were something along the lines of,
'When you use Lookups, you need to make sure that stuff is well documented,
that you are smart in your relational design, and that you understand how
they work', then I would not have said a word.  But instead, the general
comment was 'Lookups are bad, because they bloat and slow down a database,
and they are confusing.'.  Very broad reasoning, and to a beginner, that
could easily cement the idea that they should never use them.  However, the
'bloat' is just part of indexing, which is something you definitely
shouldn't avoid, indexes are good. The slowdown has yet to be proven
(haven't seen it myself at all, in fact I proved otherwise), and the
'confusion' part is a catch 22, so it can't really be used.  What may be
confusing to some users, can be very helpful for others.  

I use ADO far more then I use DAO.  I use ADO because I like the flexibility
to switch between datasources.  It makes my code a lot more portable.  Do I
pass on to others that DAO is bad, because I prefer ADO?  No.  In fact, I
readily admit that DAO is native to JET, so it is more powerful when dealing
with an .mdb.  It's faster too.  Faster and More powerful definitely don't
point to 'do not use'.  However, I try to explain that unless I absolutely
need speed and/or power, I go for portability.  With today's computers, the
speed isn't nearly as critical in most of the stuff I develop, and the power
only comes into play when there is specific stuff I need to do, which is
rare, that DAO can do, and ADO can't.  I leave it up to the learner, to
determine what their preference is going to be.

Have you ever heard an 'expert' tell a 'newbie' that field sizes are
important to prevent database bloat?  I have.  Not kidding.  I have seen so
many databases where you can tell someone went beserk trying to 'maximize'
their field design by messing with text field sizes.  Hopefully the expert
wasn't a complete moron, and actually knew that text fields take up only the
space they need, (data plus one byte), and that using the text field size
option merely limits what the user can actually put in.  If that's the case,
the 'expert' just prefers to control data input at the table level, instead
of at an interface level.  That's just a preference.  But to gloss over the
reason, by saying something that 'makes sense', but isn't exactly true, is
wrong.  That 'newbie' is walking away thinking they have learned a fact, but
instead, they were misled.

I'm not pointing this at you Gustav, not at all, just on my soap box at the
moment.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Friday, March 26, 2004 11:04 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Lookup Fields in Table Design


Hi all lookuppers

Maybe I'm dense but - as mentioned by Ken - if you really need the
feature of lookup fields (and I did once for some superuser
administrative tasks performed once a year or never, thus not
justifying design of eight forms) you simply - in a separate frontend
database - create some queries retrieving exactly the fields you need
(which excludes the ID), readable aliases for the fields, and lookups
as needed. 

Then everybody is happy. You avoid fiddling with the table design and
you can easily redesign the queries if and when needed and you take
advantage of a feature of Access which can be handy.

And this discussion becomes moot (isn't that the wording for such a
situation you use "over there"?)

/gustav


> 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

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



More information about the AccessD mailing list