Structural Dillema

[Jim Tanis]

I have many tables which every one has one or more fields that accept values
from a combo box that takes its values from other tables. To make it easier
let me display it.

table1
fields: ID,name, position->combo that looks up ID column from table2


table2
fields: ID,position_description


table3
fields: ID,product, category-> field that looks up table4

table4
fields: similar to table2, ID, category description

and so on...

So many tables have a field that looks up to another table, having
predefined "options". However, as above, table2 and table4 have no data
resemblence as positions have nothing to do with categories.
This results in dozens of tables containing option values for fields in
other tables.

BUT, the two tables (2,4) have identical structure.

So my question is:
Is it better to keep it that way, relating each lookup column to the ID
field of the "option list" table (aka position in table1 with ID in table2)
OR
Pack up all "options" in a new table (lets say table5) with three columns
(such as ID, option, tablesource) and use queries to filter the options
available to each table and relate all tables that have options residing in
the new table to the ID column of table5. This "saves" me about 30 tables
that have more or less about 10 records each, available to each table.

 

[Allen Browne]

Hi Jim. Good question, and either approach is acceptable.

Some developers prefer the "all-similar-lookups-in-one" approach because you
end up with fewer tables to maintain. The limitations of this approach are:
1. Referential integrity is not quite so tight. The engine will permit you
to create a relation between a table and an option that was designed for
another table. Not a big issue, because the combos and NotInList usually
handle it at the interface level, but it just feels that bit less safe to
me.

2. Limited indexes. Access creates a hidden index for every relationship
created with Referential Integrity. If you have heaps of these, you can
fairly easily run into the limit of 32 indexes per table. This is a fairly
serious issue.

3. Concurrency. In a multi-user environment with lots of users and lots of
combos and simultaneous updates, the all-in-one approach may be less
efficient (more chance of conflicts/retries.) Small issue, but every bit
helps in this area.

4. More queries to manage. Although you have fewer tables, you have to
create queries to pull the select the desired data out of the tables anyway,
so it's not such a gain. (For me, this is a complete non-issue: I use saved
queries for to feed the combos anyway, mainly for ease of maintenance: I can
make a single change and and have all the dropdowns for that lookup respond
consistently.)

My personal preference is to create seprate tables for each lookup. I use a
non-standard prefix of "lt" for these tables (stands for lookup table),
which means the all sort out seprately on the Tables tab of the Database
window, which solves the issue of finding tables quickly. Disadvantages:

1. More tables to stay on top of. The prefix solves this for me. (Actually,
I use it in the query window as well: lqXXX feeds combo XXX throughout the
app.)

2. More cluttered Relationships window, because more tables. Personally, I
find the individual tables placed need the table(s) that depend on it to be
more readable than lines going everywhere from a single lookup. And in a
larger database, I will usually drop the lookups out of the relationshps
view anyway. Stephen Lebans has a neat utility that lets you save different
relationship views at:
http://www.lebans.com/saverelationshipview.htm

BTW, I also use a text-based primary key where I can for these lookup
tables. 24-character is usually enough for category names, and the natural
key is just as good as an artificial key, and also avoids the problems where
combos go blank in a continuous form/datasheet if the bound column is
zero-width and the list is filtered.

Hope that helps you think through which way will be best for you.

 

[Pat Hartman]

Just so you have another point of view. I use the "all simple lookups in a
single table" method. Over the years I have developed a table, two forms,
and two reports that I automatically import into every new database. The
original concept goes back to the early 70s when I was building mainframe
applications with IMS DB/DC in COBOL so it is well tested. The table is
self referencing in the sense that one of the "tables" it contains is a
table of tables. I create separate queries for each "table" in the table
and use those to populate combos and when joins are necessary for reports or
exports that should include the text value. I do enforce RI but I agree
with Allen that this is somewhat less reliable than individual lookup
tables. I accept this trade off because it reduces my database maintenance
and eliminates the necessity of creating individual maintenance forms for
the user to manage the lookup table contents.
If you want to get more sophisticated, you can add a field to the table and
code in the table form that allows users to view all tables but keeps them
from updating or deleting certain entries.
I also include an active flag since I normally don't want to allow the user
to delete codes under any circumstances. So, this allows the old codes to
stay so that archived data remains valid but the inactive values will not be
allowed to be applied to new or updated record.