Look up tables vs. relationships design

[Jill]

In my pond database, I have look up tables which merely contain a field for
an ID (primary key, autonumber) and a field for a category (e.g., "0-25%",
"26-50%", etc., or "Dry", "Rainy", "Snowy"). These look up tables serve
merely to populate selection options for combo boxes in my data entry forms
and one look up table can be used multiple times (for multiple fields) on the
same form.

Do these IDs need to be linked with foreign keys in the data tables in the
Relationships window? What determines what needs to be defined in
Relationships (e.g., if you want to enforce referential integrity)? Or is the
thinking that you should always link any table in Relationships?

Jill

 

[Fred]

I noticed that nobody answered yet. Here's my two cents, maybe to trigger
other responding.

If you are using a table as a list to populate a dropdown/combo box to
populate a field, I see no need to define a link.

In fact, even though it's a table, and, if you use in-table look-up fields
(as the developers never do ) those links can show in the relationships
window, I would consider drop-down lists to be a fundamentally different
situation from linking tables with stored information. In the latter case,
each of the linked records is databased information about the entity which is
the record, and the linkage documents a relationship between those two
records. Incidentally, the latter "linkage" is a three step process
rather than just drawing a line:

- Create the FK field
- Put the PK value of one record into the FK field of the other. In my
view, this is the main linking process
- draw the line in the relationships window,create the line in an SQL
statement etc.

Something you already know, but a different way of saying.... If you have a
data rule which Access "referential integrity" will enforce and you want it
to do so, then you'll need to defin it in the relationships window.