Lookup Tables and Field Validation Rule Properties

[samah]

Access Novice - WinXp/Access 2003

I am in the process of designing my first database and plan to use this
mainly to teach myself Access. The purpose of this database is to store
employees data and track down employees personal info, employment
history, salary history, leave records, Visa/Residence Status (75% of
the employees of the company are expats of different nationalities) etc.
etc..

The tables in my original design contained many lookup fields but after
hanging over in this forum for quite some time now and understanding the
perils of lookup fields, I re-designed the database by removing the
lookup fields and putting them in many small lookup tables.

Now I have ended up with a single subject(employees) database with
numerous small tables, most of them directly linked to the main
employees table through Employees ID.

I feel the need to reduce the number of tables in the database since I
plan to add new features like sales order processing, invoicing etc at a
later stage. So I seek your valuable advice.

When a field value is drawn from a limited list of possible values, what
is the most efficient way of getting that value into the field? through
a lookup table or directly into the main table with field validation
rule property set to the list of all possible values? Instead of
applying the field validation rule property at table level, can it
independently be applied at form level? If yes, how?

Thank you for your time in advance.

I have seriously taken your advices given in this forum and really
started with a paper, pencil and a good eraser. So I hope you won't have
any hesitation in helping me! <grin>.

Samah

 

[Rick Brandt]

Access Novice - WinXp/Access 2003

I am in the process of designing my first database and plan to use this
mainly to teach myself Access. The purpose of this database is to store
employees data and track down employees personal info, employment
history, salary history, leave records, Visa/Residence Status (75% of
the employees of the company are expats of different nationalities) etc.
etc..

The tables in my original design contained many lookup fields but after
hanging over in this forum for quite some time now and understanding the
perils of lookup fields, I re-designed the database by removing the
lookup fields and putting them in many small lookup tables.

Now I have ended up with a single subject(employees) database with
numerous small tables, most of them directly linked to the main
employees table through Employees ID.

I feel the need to reduce the number of tables in the database since I
plan to add new features like sales order processing, invoicing etc at a
later stage. So I seek your valuable advice.

When a field value is drawn from a limited list of possible values, what
is the most efficient way of getting that value into the field? through a
lookup table or directly into the main table with field validation rule
property set to the list of all possible values? Instead of applying the field
validation rule property at table level, can it independently be applied at
form level? If yes, how?

Thank you for your time in advance.

I have seriously taken your advices given in this forum and really
started with a paper, pencil and a good eraser. So I hope you won't have
any hesitation in helping me! <grin>.

Don't worry about table propagation until you get to a VERY large number. To
answer your question you should use both a lookup table to drive a ComboBox on a
form for making entries and also use referential integrity between the data
table and lookup table to make it impossible to enter a value that is not found
in the lookup table.

 

[samah]

Don't worry about table propagation until you get to a VERY large number. To
answer your question you should use both a lookup table to drive a ComboBox on a
form for making entries and also use referential integrity between the data
table and lookup table to make it impossible to enter a value that is not found
in the lookup table.

Thank you, Rick. May be I didn't make myself very clear.

tblEmployees
-----------
empID - PK
empFName
empLName
deptID - FK
<other fields>


tblDepartment
-------------
deptID - PK
deptName

I have only 3 records in the departments tables. Isn't it efficient to
remove the department table, replace the deptID(FK) field in the
Employees table with a new field named DeptName and setting its field
validation rule to (In "DeptName1","DeptName2","DeptName3") ?

The other question I asked was, instead of setting the above validation
in the table level, can't I implement it in the Form Level. May be in
the form that I am going to use for the data entry of Employees table, I
should be able to add a combo box which could display a list of the
above values to choose from?

Hope I made myself clear this time.

thanks.

samah.

 

[Douglas J. Steele]

Thank you, Rick. May be I didn't make myself very clear.

tblEmployees
-----------
empID - PK
empFName
empLName
deptID - FK
<other fields>


tblDepartment
-------------
deptID - PK
deptName

I have only 3 records in the departments tables. Isn't it efficient to
remove the department table, replace the deptID(FK) field in the Employees
table with a new field named DeptName and setting its field validation
rule to (In "DeptName1","DeptName2","DeptName3") ?

No. What happens if DeptName4 gets created, or DeptName2 gets eliminated?
Hard-coding is seldom appropriate.

The other question I asked was, instead of setting the above validation in
the table level, can't I implement it in the Form Level. May be in the
form that I am going to use for the data entry of Employees table, I
should be able to add a combo box which could display a list of the above
values to choose from?

If it's a business rule, shouldn't it apply everywhere, not just in the one
form? Form Level validation would imply redundant rules in each form, as
opposed to in the tables, where they belong. Remember, too, that
applications should be split into a front-end, containing the queries,
forms, reports, macros and modules, linked to a back-end, containing the
tables. If your rules are in the front-end, if a different front-end gets
used, or people connect directly to the tables in the back-end, your rules
wouldn't be in effect.

 

[Rick Brandt]

Thank you, Rick. May be I didn't make myself very clear.

tblEmployees
-----------
empID - PK
empFName
empLName
deptID - FK
<other fields>


tblDepartment
-------------
deptID - PK
deptName

I have only 3 records in the departments tables. Isn't it efficient to remove
the department table, replace the deptID(FK) field in the Employees table with
a new field named DeptName and setting its field validation rule to (In
"DeptName1","DeptName2","DeptName3") ?

The difference in efficiency would be minimal. The preference for a table is in
ongoing maintenance. Should you need to add a department or change the name of
a department later on the validation rule strategy would require a design change
to the table structure whereas the lookup table strategy would merely involve
changing the data in the lookup table. In a distributed mult-user app this
advantage can be very large.

The other question I asked was, instead of setting the above validation in the
table level, can't I implement it in the Form Level. May be in the form that I
am going to use for the data entry of Employees table, I should be able to add
a combo box which could display a list of the above values to choose from?

Yes you can certainly do that and it would be what most developers would do.
But you should do that on your form *in addition to* making that a rule at the
table level, not instead of. Now the lookup table strategy's advantages are
even more pronounced here. Without it your ComboBox would have to use a Value
List since there would be no table to draw the choices from. Now if those
choices need to change you have to make design changes to your form (possibly
multiuple forms).

In addition if you have queries and/or reports where you want to display the
department name you can simply include a join to the departments lookup table to
get that information. Without it you have to include a Choose() function or
similar which would be even more things that would require design changes should
that list ever need to be altered.

 

[samah]

No. What happens if DeptName4 gets created, or DeptName2 gets eliminated?
Hard-coding is seldom appropriate.


If it's a business rule, shouldn't it apply everywhere, not just in the one
form? Form Level validation would imply redundant rules in each form, as
opposed to in the tables, where they belong. Remember, too, that
applications should be split into a front-end, containing the queries,
forms, reports, macros and modules, linked to a back-end, containing the
tables. If your rules are in the front-end, if a different front-end gets
used, or people connect directly to the tables in the back-end, your rules
wouldn't be in effect.

Ok. I got it. Thank you Douglas.

 

[samah]

The difference in efficiency would be minimal. The preference for a table
is in ongoing maintenance. Should you need to add a department or change
the name of a department later on the validation rule strategy would
require a design change to the table structure whereas the lookup table
strategy would merely involve changing the data in the lookup table. In a
distributed mult-user app this advantage can be very large.


Yes you can certainly do that and it would be what most developers would
do. But you should do that on your form *in addition to* making that a
rule at the table level, not instead of. Now the lookup table strategy's
advantages are even more pronounced here. Without it your ComboBox would
have to use a Value List since there would be no table to draw the choices
from. Now if those choices need to change you have to make design changes
to your form (possibly multiuple forms).

In addition if you have queries and/or reports where you want to display
the department name you can simply include a join to the departments
lookup table to get that information. Without it you have to include a
Choose() function or similar which would be even more things that would
require design changes should that list ever need to be altered.

Thank you, Rick.

Samah.

 

[samah]

Allow me to expand that point.

The choice between validation rule or lookup table is generally made on
absolutely number of values and its stability.

For a small stable/static set of values few in number, a validation
rule is generally preferred because it doesn't 'bloat' the schema e.g.
ISO 5218 sex codes (0 = not known, 1 = male, 2 = female, 9 = not
specified) are not likely to change any time soon and so can be safely
hard-wired into SQL DDL, SQL DML and front end code.

The states in the union and their respect codes are stable but the
number of states is likely enough to justify its own table. Note the
advantage of having a lookup table goes beyond DRI (declarative
referential integrity); the ability to create table joins should not be
overlooked.

The above is merely a 'rule of thumb'. I understand in Access there is
a case for tending towards lookup tables to be able to populate front
end controls (comboboxes, pick lists) with all possible values. Then
there is the flavour of validation rule where one stores all the valid
*patterns* as rows in an auxiliary table e.g. see CREATE TABLE
LineFeedPatterns in this thread:

http://groups.google.com/group/micr...b247f55cc9/e97257f9ca95abb3?#e97257f9ca95abb3

I don't think 'efficiency' is the word here. Instead think 'code
maintenance'.

Jamie.

Great. Thank you, Jamie.

samah.