Efficient design of Lookup Table

[GeorgeMar]

Gentle folks

I seek you help in designing a lookup table. My lookup
table, tblLookup, looks like this:

Field: LookupID LookupName LookupCode
Type: Autonumber Text Text
Index: Primary Key
Data: 23 Sales Dept1
24 Design Dept2

The related table tblEmployee has the the field Dept as
the primary key.

1. Should the foreign key in tblLookup be LookupID or the
LookupCode?

2. If the foreign key is LookupID, what happens when
someone inadvertently deletes a record; the ID is lost
forever from the lookup table and the tblEmployee will
have to be rebuilt?

3. If the foreign key is LookupCode, should the
lookupCode become the indexed primary key?

4. Does having the lookup tables in the Front End help
the network traffic?

Any advice on better design will be much welcomed.

many thanks
george

 

[MikeC]

See MC> below.

-----Original Message-----
Gentle folks

I seek you help in designing a lookup table. My lookup
table, tblLookup, looks like this:

Field: LookupID LookupName LookupCode
Type: Autonumber Text Text
Index: Primary Key
Data: 23 Sales Dept1
24 Design Dept2

The related table tblEmployee has the the field Dept as
the primary key.

1. Should the foreign key in tblLookup be LookupID or

the LookupCode?

MC> I would almost always recommend a numeric ID field
over a descriptive text field for joining tables. One of
the most common problems with using text fields is that
the text has a meaning, spelling, etc. and people often
wish to modify the text to change the meaning or correct
the spelling. This type of change can break the link
between related records if such updates are not
automatically cascaded, etc. On the other hand, numeric
IDs enable you to avoid this issue and are also faster to
process.

2. If the foreign key is LookupID, what happens when
someone inadvertently deletes a record; the ID is lost
forever from the lookup table and the tblEmployee will
have to be rebuilt?

MC> If there is a risk that users will inadvertently
delete records in the lookup table, then controls should
be implemented to prevent this sort of problem. Here are
some options to consider:

1) Set the form's "Allow Deletions" property to "No"
assuming the lookup table has its own form.
2) Enforce referential integrity amongst your tables.
This change alone will disallow orphaned records.
3) Hide the tables and queries.
4) Hide the database window via Tools/Startup.
5) Replace the built-in menu with your own custom menu(s)
containing only the options you want to make available to
your users.
6) Implement work group security. Microsoft's security
FAQ is available at the below link:

http://support.microsoft.com/default.aspx?scid=/support%
2faccess%2fcontent%2fsecfaq.asp

Additional options are available. The above ideas were
just the first few that came to mind.

3. If the foreign key is LookupCode, should the
lookupCode become the indexed primary key?

MC> Yes, otherwise, Access won't understand how to
uniquely identify the "parent" (lookup table) record.

4. Does having the lookup tables in the Front End help
the network traffic?

MC> Yes. However, you will need to devise a way to keep
the lookup tables current/synchronized. Otherwise, if you
update the lookup table in one FE, but not in others,
records can be added to your main table(s) that reference
lookup table records that the other FEs don't have and
thereby cause errors for users who have the old FE.

 

[Guest]

Mike

That was a most detailed response. Thank you!

George