David Fenton Associates

Lookup Administration Demo Database


Help Pages
About DFA

Download a Zip file with the demo database (version 1.0001a, October 12, 2002).

The purpose of this demo is to show two techniques that I use in my apps:

  • The use of a common lookup table for small-scale lookups.
  • A form that allows the user to pick and set colors for the UI of an application.

The first part of this demo was designed to solve two problems:

  1. Reducing the number of small lookup tables in an application.
  2. Making it easy to provide an administrative interface for maintenance of lookup values.

This approach is most appropriate for lookups where it is not absolutely essential that referential integrity be maintained at the engine level.

It has always bothered me to see dozens of small lookup tables in an application, especially when many of those have only two or three columns and only a handful of rows. So, for me, combining all those little lookup tables into one has the advantage of reducing clutter.

But the big payoff is that it allows a user single interface to administer a whole set of lookups, without the need to create a separate user interface for each lookup table.

Now, obviously, if you make all your lookup tables have the same number of columns, or even the same field names, then you could utilize a single form for editing a whole host of lookup tables. But once you've done that, you are bordering on a violation of normalization rules. Ideally, all those things that are the same entity and have the same set of attributes belong in a single table with a column added to distinguish the different types of the various records, rather than using the table to segregate the different types, all of which have the same field names/types.

The problem with my solution is that it works extremely well for small numbers of columns but becomes fussier with more columns. The demo shows just how fussy it can get in terms of adjustments to the columns. One other significant drawback is that in this demo I don't actually validate proper data types. In earlier versions of this I allocated separate columns for text, numeric and date values, but found that it wasn't really worth the effort. To implement something like that would actually require storing meta-data about each of the columns somewhere, and as most of my lookups are simply text-based, I've only done it this way. It is a valid criticism of this entire approach.

The second part is a demonstration of a form to allow end users to change color settings for forms. It uses a class module as a wrapper around an array that is initialized from values stored in the general lookup table. It is included only to show that you can also launch specific forms for particular lookup types, so that this omnibus form can be used as a common interface to multiple more complex forms specific to other dedicated lookup tables.

Instructions for Use

  1. Download Zip file with the demo database (version 1.0001a, October 12, 2002).
  2. Unzip the file to a folder of your choice.
  3. Open LookupAdmin.mdb.
  4. The code has lots of comments in it, some of which point out areas that need extension, revision or strengthening.
Home | Training | Software | Support | Help | Partners | About DFA

©2002, David Fenton Associates. Created October 11, 2002, updated October 16, 2002.