[AccessD] Lookup Fields in Table Design

And did you try it using *forms*, Drew.  That's what users tend to deal
with, isn't it?  And that's where my experience says they're a bad idea
(well, users are often a bad idea, but I'm talking about lookups.)

As far as your "advantage",  I don't let my users come within a mile of
my tables, so where is the advantage?  They *can't* manually enter
records, period.

Charlotte Foust

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] 
Sent: Thursday, March 25, 2004 1:10 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Lookup Fields in Table Design


Okay, the three reasons given so far for not using the Lookup feature in
your table designs are:

#1: Lazy developer tool, which can confuse users.

#2: Performance Decrease.

#3: Bloat

Okay, for number one, I personally don't see a point in arguing against
that, because it's too darn easy to confuse users.  If we didn't use
something that could confuse a user, we would have to kick our computers
out the door! LOL (sorry, couldn't resist).

Now for Number 2.  Did a simple test.  I created a database with a
simple lookup table (with a list of states) and then added a table that
had a 'State' field.  Copied that database, and then added a lookup for
the State field, using the tblStates table.  Then I created a little
code which added 200,000 records to tblCustomerInfo (my 'dummy' table).
BOTH databases took about the same amount of time to enter records.
(used an INSERT INTO statement.....that took a while, probably would
have been faster to use the Add method of a recordset.  Either way, both
processes took about the same amount of time.).  With 200,000 records in
the table, in both databases, opening the table in datasheet view takes
the same amount of time in either database.  Thus, no performance
decrease at all.

Number 3.  Well, I checked the size of both databases along the whole
process.  (I did put the lookup table in both databases, so they should
have been the same 'size').  Guess what, they were.  Started at 92,160
bytes
(each) with no data other then the US States, and after 200,000 records,
both databases were 5,869,568 bytes.  I even manually added records
(about a dozen), and both databases were still the same size (in bytes),
though I didn't write those numbers down.

However, and this is a BIG however.  I found a distinct advantage to
using a Lookup field.  In the database with the Lookup field, I had set
the Limit to List property to Yes.  I then tweaked my code, and added
"XX", instead of a random state, for 10 records.  BOTH databases added
10 records with XX in it.  Going to the Non-lookup database, I could
still manually enter a record with XX as the State.  Going to the Lookup
database, I could NOT manually enter XX, because it wouldn't let me
enter a state not on the list.  Setting the field validation for <>"XX"
in both tables, prevented the code from adding new records with that
value.  So that is a very interesting advantage. It allows you to
enforce 'user' data entry 'rules', while allowing code inserted/modified
data to bypass.  That could be handy.

One last note.  The 'lookup' capability does NOT have to be a SQL
statement. Combo boxes and listboxes can both use Callback functions.
This DOES apply to Lookup fields.  (did a test. Used a previously built
Callback function that lists the reports in a db for list/combo box, and
added it to a field's lookup property.  sure enough, it listed the
reports when entering that
field.)

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