Lookup fields in tables bad?

[Robert5833]

Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of lookup
fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created lots
of lookup fields in my tables...lots and lots of them.

My question is two-fold. First; what is the risk and what bad things can
happen to the wayward soul who unwittingly builds lookup fields into his
tables, and second; if such practice should be avoided like the plague, how
does one go about eliminating them after the fact?

Maybe one more question; I also use field lists and value lists at the table
level. Is this the same, and just as bad as having an SQL lookup field in a
table?

In a copy of a database I’m working on now, I changed all of the table level
lookup fields (SQL type) to straight text boxes and I’m waiting now for that
copy to implode…but it hasn’t yet, and all of my forms are working correctly.
(I have lots and lots of lookups as the Row Source property.)

Since I changed the table fields to Text Boxes, I haven’t tried to enter any
new data…maybe Armageddon awaits me there?

I rarely enter data directly into my tables; and even when I do it's only
after a first pass at building one, and just before I create a form for data
entry purposes. Since my forms all have lookup fields in them, will my house
of cards tumble only if I were to enter data directly into a table?

If anyone has some good insight on this, or would simply to poke fun at me
for my ignorance or even just offer anecdotal comments on what may be my
impending doom, I would appreciate any input at all.

Thank you in advance.

Best regards,
RL

 

[Rick Brandt]

Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created
lots of lookup fields in my tables...lots and lots of them.

All of the advantages of lookup fields in tables are only there if you
work directly with tables. Nobody who develops serious apps for people
create those apps so users can work directly in tables.

They use forms and reports for the interface and when you do that lookup
fields in tables lose all their advantages and pick up a few
disadvantages. The most notable is that your tables display data that is
not really there which can be confusing when using them in queries and
forms and reports.

 

[Allen Browne]

Okay, lets see if we can clarify things for you here.

In table design, if you choose a field of type "Lookup Wizard", you fire up
a wizard that many of us don't like. We tend to avoid this wizard because of
its side effects. You have probably read this:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

But no one is saying that you should not use foreign keys. Every non-trivial
database has lookup tables -- small tables that contain the valid values for
a field in a table that looks them up. That is the only way to correctly
normalize your data.

There is a debate about whether the lookup table should have a numeric field
as its primary key (in which case your foreign key field will also be a
Number), or whether you should use a natural key (Text type.) If you have a
lookup table for categories, each category name will be unique. If they are
also relatively brief (just a couple of dozen characters), it makes sense to
me to use that as the natural key instead of introducing an artificial key
(such as an AutoNumber.) Sounds like you may have done that in some cases.

Next the question arises as to whether your main table should use a text box
or a combo box for the foreign key field. Combos are great on forms, but
many developers avoid them in the table because it masks what data is really
here. For example, if you use the Lookup Wizard, you get a field of type
Number, but it displays the text. That's confusing for developers and
newbies alike: you actually have to open the table in design view in order
to build a WHERE clause on the field, because you don't know what delimiters
you need just by looking at the data.

You also raise the possibility of using value lists rather than a lookup
table. IMHO, that's the worst possible solution. It's trivial to let the
user add more records to a lookup table, but modifying the items in a lookup
list requires design changes. For more info on this, see:
http://allenbrowne.com/ser-27.html#ValueList

So, FWIW, my recommendations would be:
- Do use foreign keys.
- Avoid the Lookup wizard.
- Use natural keys where appropriate.
- Use combos in your table only rarely.
- A Value List in a combo in a table is too inflexible.

 

[David W. Fenton]

here is a debate about whether the lookup table should have a
numeric field as its primary key (in which case your foreign key
field will also be a Number), or whether you should use a natural
key (Text type.) If you have a lookup table for categories, each
category name will be unique. If they are also relatively brief
(just a couple of dozen characters), it makes sense to me to use
that as the natural key instead of introducing an artificial key
(such as an AutoNumber.)

I would add to all those things the question of whether the values
are ever going to be updated. If they can change, then I'd use an
Autonumber surrogate key, instead of the natural key.

 

[Robert5833]

Thank you Rick! Great info! I'll post a reply to all of sorts...

Robert

 

[Robert5833]

Hi Allen,
Thanks so much for the great outline on this subject, and things are much
clearer to me now.

In fact I have frequented your site and others, trying to gain what
knowledge I can from the pros, and I spend hours reading the helpful posts
here, and those interesting and helpful resources you and others make
available to guys like me who are just beginning.

And I thank everyone in this group for their generosity, and their
willingness and enthusiasm to help others. These discussion groups have been
an invaluable resource for me.

Back to the conversation at hand; as for primary and foreign keys, that
hasn’t been too much of a struggle for me, but I’ve been less apt to use a
natural key and almost always go with the Autonumber variety. But given your
input I’m beginning to see that the options are there, although it will take
me a while to understand when one is more desirable than the other.

My bigger struggles have been with all of the other lookup fields that I
build into my tables that over time I began to realize didn’t add any value
at all (I do use forms exclusively for data entry, and all of that
functionality is in them as well). Not to mention (and as you and others have
pointed out) the problems that result in queries and code when the field
properties aren’t what one thinks they might be given the data they display.

My overuse of lookup fields in tables seems to be a carryover from a time
when I didn’t know how to use forms well enough, and didn’t know how to build
the power of lookup fields and controls into them.

That and the fact that the Lookup Field Wizard was just so handy and easy to
use… And interestingly, for all of the hours I have spent reading the
material on the MS Access DN site, not a single mention that I can recall of
the pitfalls?

I’d like to think that I only use lookup fields in tables when they are
foreign key references, but just given the quantity of them in my tables I’m
not sure I understand when a foreign key could be a text box, or when it must
be a combo box with lookup? (I would be grateful if you or someone could
clarify that point for me; what is the rule, logic, or rationale for one or
the other.)

With regard to your comments and those of others about the use of Value
Lists, Field Lists, List Boxes and the like, I’m much clearer now on their
limitations. While I have used them on occasion, it has only been when I have
a short list of selections, and only when I know those selections aren’t
likely to change. When I do use them, I incorporate the Limit to List
functions to keep them from growing. But I have found it difficult to
manipulate or process data with them, so I have for the most part left them
behind opting for an additional table instead and now I may eliminate them
altogether.

To be sure, I have a long, long way to go; but I look forward to reading
more of your writings and the writings of others on this and other topics.

I don’t want to abuse or monopolize anyone’s time here, so I’ll go back to
reading everything I can find on this subject, and post again if I have any
specific questions.

Thanks again for the post!

Best regards,
Robert

 

[Robert5833]

Hi David,

Thank you for the reply. I use almost exclusively the Autonumber surrogate
key; maybe to a fault? On occasion I find that a table with just a few
records might lend itself to use of a natural key, but I don't know enough
about what I can, or cannot do with the data later, so to be safe I opt for
the Autonumber.

I suppose as time goes by and I gain a lot more knowledge than I have today,
that point or question will resolve itself.

Thanks again for the post!

Best regards,
Robert

 

[John W. Vinson]

I’d like to think that I only use lookup fields in tables when they are
foreign key references, but just given the quantity of them in my tables I’m
not sure I understand when a foreign key could be a text box, or when it must
be a combo box with lookup? (I would be grateful if you or someone could
clarify that point for me; what is the rule, logic, or rationale for one or
the other.)

A foreign key can *never* be a textbox, nor can it ever be a combo box!

Textboxes and combo boxes are display tools. Keys (primary or foreign) are
fields in a Table. Those fields can be displayed - on a form or report, or
thanks to some questionable design decisions by the Access development team,
in tables - but don't confuse the data *storage* with the data *presentation*.
Primary and foreign keys are logical entities defining the relationships
between data.

A Primary Key is a field - or a set of up to ten fields - which uniquely
identify a record in a table.

A Foreign Key is a field - or a set of up to ten fields - containing the same
data as the Primary Key of some other table, and serving as a link to that
table.

Any field (whether it's a primary key, a foreign key, or not used as a key at
all) can be displayed using the various data display tools that Access
provides - or for that matter, not displayed at all. For instance, if I use an
autonumber primary key (which I do), it will NOT be displayed to the user at
all; the same applies to the Long Integer foreign keys which are related to
it.

The natural key/surrogate key argument can get long and heated. I'll use
natural keys when they're short, stable, and unique, which actually leaves out
a lot of fields that otherwise might be candidates. People's names, for
example, fail on all three counts! Some examples where natural keys may be
appropriate are State two-letter codes (if your addresses are entirely or
mostly in the United States and Canada); there's only one state/province with
ID as a code, two letters is suitably short, and they rarely change (Canada
added NU and NT, Nunavut and Northwest Territory, a few years ago); five digit
ZIP codes are another reasonable choice.

 

[Allen Browne]

Natural verses artificial keys
=====================
You'll find diverse views on this, and I encourage you to read contrary
views to get a balanced perspective.

I personally find natural keys handy for several reasons:

a) If you are going to require and uniquely index a single field in a lookup
table (such as a category or type), I see no point in adding another
artificial key as well. Keep it as simple as possible.

b) The foreign key (i.e the field in the other table that looks up this Text
field) is also Text. It's humanly meaningful, and sometimes reduces the
number of additional lookup tables you need in your queries.

c) The interface is easier/more flexible, avoiding several problems that
occur if a combo's display column is not the bound column. For example:

- Combo doesn't go blank in datasheet/continuous form view if you filter its
RowSource.

- Filters based on the combo are less problematic (and differ based on
version of Access.)

- Limit to List is optional (where you don't enforce referential integrity,
or where the combo's RowSource is filtered.)

- All versions of Access have display bugs where you filter a combo's
RowSource (e.g. in Form_Current.) You avoid these problems if the bound
column is the display column.

In summary, using a simple, short, required, unique text field as a natural
key is a simpler design, and avoids some limitations, complexities, and bugs
in the Access interface.

Cascading updates/deletes
=====================
If you do decide to use natural keys, the question arises about whether you
should use cascading updates. Again, informed people like Tony Toews and
David Fenton don't like them and you've been referred to Tony's page
explaining why.

I do use them (sometimes.) If a category has been misspelled, it makes sense
to me to allow the user to correct the spelling in the form where they
manage the lookup values, and have JET cascade the update to the related
tables.

I almost never use cascading deletes on this kind of data. You really don't
want all the records removed from your main table just because some idiot
thought the category was no longer useful and decided to delete it. On
occasion, I will use cascade-to-null. This means that the foreign key is set
to Null in the related table when you delete the category from the lookup
table, so the records are not lost: they become uncategorized. If you want
to read more about that, see:
Cascade to Null Relations
at:
http://allenbrowne.com/ser-64.html

Overusing lookups
==============
Not sure what examples you have in mind. All my databases have numerous
lookup tables (typically between 5 and 40, depending on the complexity of
the database.)

The only cases where I don't bother creating a lookup table is where the
choices are extremely limited and set in concrete, such as "M"/"F"/Null for
Gender, or True/False/Null (using a Number field), of a field that holds a
number represnting a data type (values for dbText, dbLong, etc.)

Interfacing fields
============
John has explained the view that tables are to hold fields so combos don't
belong there at all. That's a rule I follow about 90% of the time, but I
sometimes use a combo in the table - mostly for the examples above where the
RowSource is a Value List.

Some of this stuff you decide by experience.
Have a go at different approaches, and see what works for you.

 

[Fred]

Re: "All of the advantages of lookup fields in tables are only there if you
work directly with tables." to that I'd add "or queries"

 

[David W. Fenton]

Re: "All of the advantages of lookup fields in tables are only
there if you work directly with tables." to that I'd add "or
queries"

Creating lookups in a query is just fine, seems to me, as it saves a
join. On the other hand, it has all the problems that lookups have
in tables if you use that query in the FROM clause of another query.

 

[John W. Vinson]

Creating lookups in a query is just fine, seems to me, as it saves a
join.

Does it though? I was under the impression that a lookup just creates a join,
"under the covers".

 

[David W. Fenton]

Does it though? I was under the impression that a lookup just
creates a join, "under the covers".

I think that in certain circumstances, retrieving two recordsets
(the query recordset and the combo box recordset) is going to be
more efficient than retrieving the same data joined in a single
recordset. If the combo box has lots of records (10s of thousands),
it will definitely be more efficient, as Rushmore will kick in for
the dropdown (i.e., retrieving only as many records have been
requested for viewing), whereas with a join, the whole index is
going to have to be retrieved and processed by whatever kind of join
is used.

 

[Fred]

My point is that in-table lookup fields don't just add dropdown functionality
to direct views of tables, they add otherwise unavailable functionality to
query views.

It may be that 95% of the time that one would be better off avoiding
in-table lookup fields, (and we very much appreciate this advice from the
experts) but 95% is not 100% so it should be taken as such rather than as a
categorical rule.

Some of our situations are in the 5%.

 

[David W. Fenton]

My point is that in-table lookup fields don't just add dropdown
functionality to direct views of tables, they add otherwise
unavailable functionality to query views.

It may be that 95% of the time that one would be better off
avoiding in-table lookup fields, (and we very much appreciate this
advice from the experts) but 95% is not 100% so it should be taken
as such rather than as a categorical rule.

Some of our situations are in the 5%.

I strongly disagree. Lookup tables do not belong in tables at all.

In queries, I don't it's much of an issue, but should be used only
in queries that are used by the developer, since one should never
just dump a user into a query datasheet. If you're using a saved
query as a recordsource of a form, then you'll end up with the same
problems that you get with lookup fields in table definitions.

So, for me:

1. Tables: avoid 100% of the time

2. Queries: avoid 100% of the time for recordsources.

 

[Fred]

David,

So, if my mom, who knows Access just a little, takes a few minutes to make
an mdb to put her grocery list on, another minute for the wizard to make a
dropdown list consisting of the four sections of the grocery store, she:

- should never look at the data in a table or query directly?

- if she wants dropdown cabability, she should hire a developer for her for
her gocety list who knows how to do that in forms with combo boxes etc?

I have this debate with my son who thinks that the rules that are good for
his 200,000,000 record SQL databases should be categorical rules, i.e. rules
for my mom's grocery list.

Many developers presume the situation and all Access uses are developed
applications. I.E. a substantial investment to make a an application
suitable for use by lots of people who know nothing about Access and have
poor data entry disclipline.

In our company's case, we have many databases which are quickly put together
by one person than then used by just 1-4 engineers / power users. They make
lots of use of doing searches (different every time) by putting criteria in
the query grid. I don't know any other way to give them that functionality
without using the query directly.

That's the other "5% " In my opinion, really good practices such as you
describe should be strong influences on design, not necessarily categorical
rules.

 

[Keith Wilby]

So, if my mom, who knows Access just a little, takes a few minutes to
make
an mdb to put her grocery list on, another minute for the wizard to make
a
dropdown list consisting of the four sections of the grocery store, she:

- should never look at the data in a table or query directly?

- if she wants dropdown cabability, she should hire a developer for her
for
her gocety list who knows how to do that in forms with combo boxes etc?

My 2p worth: she should use Excel. Or the back of an envelope like I do

Keith.

 

[David W. Fenton]

So, if my mom, who knows Access just a little, takes a few
minutes to make an mdb to put her grocery list on, another minute
for the wizard to make a dropdown list consisting of the four
sections of the grocery store, she:

- should never look at the data in a table or query directly?

No. She should create a form (wizards, again).

- if she wants dropdown cabability, she should hire a developer
for her for her gocety list who knows how to do that in forms with
combo boxes etc?

No. Wizards.

I have this debate with my son who thinks that the rules that are
good for his 200,000,000 record SQL databases should be
categorical rules, i.e. rules for my mom's grocery list.

If lookup fields didn't cause problems, then I wouldn't be against
them, but the kinds of problems they cause are mystifying to novice
users, and best avoided.

And editing in tables is a bad idea for those users, too. It's not
like it's hard to create a form.

Many developers presume the situation and all Access uses are
developed applications. I.E. a substantial investment to make a
an application suitable for use by lots of people who know nothing
about Access and have poor data entry disclipline.

In our company's case, we have many databases which are quickly
put together by one person than then used by just 1-4 engineers /
power users. They make lots of use of doing searches (different
every time) by putting criteria in the query grid. I don't know
any other way to give them that functionality without using the
query directly.

That's the other "5% " In my opinion, really good practices such
as you describe should be strong influences on design, not
necessarily categorical rules.

Well, we disagree. I think you're buying your users future problems
because you lack the knowledge to do it right. I've been there and
have spent years fixing up the terrible things I did back in the day
when I didn't know any better. The sooner you learn to do it right,
but fewer of those future problems you will be creating.

 

[Robert5833]

Hi Allen; et al,

Thanks so much for the great outline on this subject, and I appreciate very
much your taking the time to help me understand.

I would have replied sooner, but I’ve been down hard with the flu.

In fact I have frequented your site Allen, as well as others posted in this
discussion group trying to gain what knowledge I can from the pros. I’ve
spent hours reading the helpful posts here, and those interesting and helpful
resources you and others make available to guys like me who are not.

And I thank everyone in this group for your generosity, and for your
willingness and your enthusiasm to help others.

To be sure, I have a long, long way to go; but I look forward to reading
more of your writings on this and other topics.

Thanks again for the posts and the help!

Best regards,
Robert

 

[Fred]

Hello David,

Thanks for taking the time to give that insight. But I think that we're
talking about two different things / situations.

The "my mom" part was metaphorical, but your post didn't cover the real
example I gave......there are lots of databases out there where there is no
developer, and which are created in minutes and used by only on or two power
users. The don't look at data in table views, but often look at it query
views, setting completely different sort and filter criteria on different
fields with each use.

I think that the newer post in this section by Albert D. Kallal brings
together what both of us are saying.