Best Data Model?

[Harper]

Hello,
I have been reading about normalizing but still find myself a bit
stumped when it comes to my own real-life needs. I got some great help
here and am hoping that I can come back with another request...There
are three sets of teacher employees 1) licensed with license A and
seeking license B 2) not licensed with license A and seeking license B
3) Not seeking license B. Certain data is attached to the employee
depending on what set she is part of (#1 or #2; #3 is the default set
and does not have unique data attached to it); on the other hand, all
employees share other types of data (of course all employees have home
addresses and telephone numbers, but what I am getting at here is the
kind of data that has specifically to do with requirements they need to
fulfill to receive license B).

I currently have:

tblEmployee
EmployeeID
EmployeeAddress
BirthDate
HireDate
TerminationDate
DepartmentID
PayRate
LicenseStatus (pull-down; options are "With License A, Seeking
B;Without License A, Seeking B;Not Seeking B)--this is here for the
data entry form-->upon using a pull-down menu to choose an option,
after update code triggers the form to reveal a subform depending on
the choice)

Here is an example of the kinds of data that is attached to employees
seeking license B whether they are a part of set 1 or 2:

LicenseSeekingRequirements:
CoreClasses
ExtraClasses
Observation
Interview
ApplicationDate

Here is some of the data that is attached to employees seeking license
B depending on whether they have license A or not:

HaveLicenseA:
PraxisIISpecialtyTestScore
200TeachingHours

Haven'tLicenseA:
PraxisITestScore
PraxisIIGeneralTestScore
PraxisIISpecialtyTestScore
EducationClass
400TeachingHours

To make a long story short, I cannot figure which is best...to create
tables which exactly duplicate the structure above (tblHaveLicenseA and
tblHaveNotLicenseA, plus tblLicenseSeekingRequirements) with columns as
shown (PraxisITestScore,PraxisIIGeneralTestScore, etc for
tblHaveNotLicenseA), or to have tables that group tests together
(tblPraxisTests with columns PraxisITestScore,PraxisIIGeneralTestScore,
etc) and teaching hours together (tblTeachingHours with columns
TeachingSite,SiteContact,HoursCompleted). In the latter scenario, I
would use a form to drive the structure--I mean, the form (the
pull-down menu,which indicates if an employee is seeking B and has A,
is seeking B and hasn't A, or isn't seeking B, and inputs that value
into tblEmployee) would control data entry input and thereby structure
data because the data entry person could only SEE subforms in the main
data entry form that have data entry fields that ONLY pertain to the
employee and what set she is in. So, tblPraxisTests would have columns
with null values, because for record "SarahSmiley," who is seeking
license B and has license A, there would only be a value in
"PraxisIISpecialtyTestScore".

I'm just not sure which is the better approach and would appreciate the
input of wiser, more seasoned folks.

Thanks much in advance,
Harper

 

[Jeff Boyce]

Another approach I've used for "certification" is to have a list (i.e.,
table) of requirements for each "level" of certification, a table of
persons, and a table of "completions" (person satisfying requirement X on
date Y, with ....).

If you create a query that sees how many cert. requirements are NOT met, you
have a list of people who AREN'T certified at any particular level.

It's akin to tracking a series of events or steps that each person may (or
may not ) have completed.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Harper]

Thank you very much for the input. So the joining table--the table of
completions--has the employeeID and a requirementID with date fields?
That sounds like a better approach than what I had come up with. More
normalized. Thanks again, Harper

 

[Pat Hartman\(MVP\)]

Combining two meanings in a single field can lead to problems. Technically,
don't you actually have 9 values rather than just three? A - have, seeking,
not seeking and B - have, seeking, not seeking. The combination of three
values for A times the three values for B = a total of 9 combinations.
I would use separate fields for A and B.