[AccessD] Lookup Fields in Table Design

John Bartow john at winhaven.net
Thu Mar 25 15:19:59 CST 2004


Slam and dunk!

5 point sayers!

Score: 2 nay-sayers / 7.5 sayers

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Thursday, March 25, 2004 3: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
--






More information about the AccessD mailing list