Lookup Tables and Field Validation Rule Properties

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

samah - 21 Nov 2006 12:44 GMT

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

Reply to this Message

Rick Brandt - 21 Nov 2006 12:57 GMT

> Access Novice - WinXp/Access 2003
>
[quoted text clipped - 30 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

samah - 21 Nov 2006 14:17 GMT

>> Access Novice - WinXp/Access 2003
>>
[quoted text clipped - 36 lines]
> 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.

Reply to this Message

Douglas J. Steele - 21 Nov 2006 14:57 GMT

> Thank you, Rick. May be I didn't make myself very clear.
>
[quoted text clipped - 15 lines]
> 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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

samah - 21 Nov 2006 15:03 GMT

>> Thank you, Rick. May be I didn't make myself very clear.
>>
[quoted text clipped - 33 lines]
> 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.

Reply to this Message

onedaywhen - 22 Nov 2006 09:12 GMT

On Nov 21, 2:57 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> > Isn't it efficient to
> > remove the department table, replace the deptID(FK) field in the Employees
[quoted text clipped - 3 lines]
>
> Hard-coding is seldom appropriate.

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/microsoft.public.access/browse_frm/thread/bb1ff4b
247f55cc9/e97257f9ca95abb3?#e97257f9ca95abb3

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

Jamie.

--

Reply to this Message

samah - 22 Nov 2006 15:58 GMT

> On Nov 21, 2:57 pm, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
[quoted text clipped - 37 lines]
>
> --

Great. Thank you, Jamie.

samah.

Reply to this Message

Rick Brandt - 21 Nov 2006 15:02 GMT

> Thank you, Rick. May be I didn't make myself very clear.
>
[quoted text clipped - 15 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

samah - 21 Nov 2006 17:15 GMT

>> Thank you, Rick. May be I didn't make myself very clear.
>>
[quoted text clipped - 42 lines]
> 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.

Reply to this Message