Test scores one to many?

[harperwork18]

I have a couple of basic questions, hope you can help.
I have an employees record set. The employees take computer tests. If
the employee came into employment before 2004, he or she takes one kind
of test. If the employee came into employment after, it is a different
test. The pre-2004 test was a simple pass/fail. The post-2004 has
scores.

I am not sure how to connect the test scores to the employee record
set. The employee takes one or the other test; but obviously many
employees can take the same test. Is something like the following the
best way to handle this?

tblEmployee
EmployeeID
EmployeeAddress
BirthDate
HireDate
TerminationDate
DepartmentID
PayRate
TestID

tblTestID
OldTestWord (Yes/No)
OldTestInternet (Yes/No)
OldTestSpreadsheet (Yes/No)
OldTestPresentation (Yes/No)
OldTestCompletion (Date)
NewWordScore (number)
NewInternetScore (number)
etc.

This seems very clunky to me. Can you help me fine tune? Also, I have
seen some people put the address in an employee table and some people
separate. Thoughts? Opinions?

Thank you,
Harpo

 

[Bill Edwards]

tblEmployee
EmployeeID
EmployeeAddress
BirthDate
HireDate
TerminationDate
DepartmentID
PayRate

tblTest
TestID
TestName
OldOrNew Yes/No

tblEmployeeTest
EmployeeId
TestId
Score: This could be handled a variety of ways to accomodate the pass/fail
vs the actual mark
DateTaken

 

[Harper]

Thank you, that makes a lot of sense to me. I had problems visualizing
it, even with pen to paper.

 

[Harper]

Bill, Oops, actually, I think I did not explain well enough. There is
an old test and a new test and which test the employee takes depends on
when he or she entered employment. If the tests had one score, your
suggestions would work perfectly. But what I was (badly) getting at is
that each test then breaks down into test areas, each with its own
yes/no attribute (for the old test) and score attribute (for the new
test).

So what I've come up now is the following.

tblEmployee
EmployeeID
EmployeeAddress
BirthDate
HireDate
TerminationDate
DepartmentID
PayRate

tblTest
TestID
TestName
UseBegan (date, to indicate when the test was put in service)
UseEnded (date, to indicate when the test was decommissioned)

tblEmployeeTestScores
EmployeeID
TestID
Word (old test; yes/no)
Internet (old test; yes/no)
Spreadsheet (old test; yes/no)
Presentation (old test; yes/no)
WinBasics (new test; score)
Networks (new test; score)
FileManagement (new test; score)
MSWord (new test; score)
MSExcel (new test; score)
MSAccess (new test; score)
MSPowerPoint (new test; score)

So essentially, there would always be null fields in the last table,
because if the employee took the old test, the new test scrore fields
would be blank; if the employee took the new test, old test yes/no
fields would be blank.

I think the tables are coming close to being there, but I do not know
if having null fields is a bad way to go about this.

Thanks for any more help you can provide.

 

[Chad]

You should not have any fields in a table that do not directly relate to the
name of the table unless it is a foreign key.

Word (old test; yes/no)
Internet (old test; yes/no)
Spreadsheet (old test; yes/no)
Presentation (old test; yes/no)
WinBasics (new test; score)
Networks (new test; score)
FileManagement (new test; score)
MSWord (new test; score)
MSExcel (new test; score)
MSAccess (new test; score)
MSPowerPoint (new test; score)

These are all tests, so they would be listed as records in the tblTest
table. They would not be fields in the tblEmployeesTestScores table. Then,
the only fields needed in the tblEmployeeTestScores would be:

EmployeeID
TestID
TestScore

 

[Harper]

Hi Bill, Oops, actually, I think I did not explain well enough. There
is
an old test and a new test and which test the employee takes depends on

when he or she entered employment. If the tests had one score, your
suggestions would work perfectly. But what I was (badly) getting at is
that each test then breaks down into test areas, each with its own
yes/no attribute (for the old test, which only required they pass) and
score attribute (for the new
test, which requires actual scores). So what I've come up now is the
following.

tblEmployee
EmployeeID
EmployeeAddress
BirthDate
HireDate
TerminationDate
DepartmentID
PayRate

tblTest
TestID
TestName
UseBegan (date, to indicate when the test was put in service)
UseEnded (date, to indicate when the test was decommissioned)

tblTestAreas
TestID
TestAreaName (i.e., MSPowerpoint, FileManagement, BasicNetworking)
TestAreaID

tblEmployeeAndTestScores
EmployeeID
TestAreaID
Score

Is this normalized...or busywork? Perhaps tblTest and tblTestAreas can
be merged..I'm not sure I need UseBegan and UseEnded...I mean, I have
the attributes just in order to keep track of which employee should
take which test (and the test areas that correspond), but I could also
eliminate UseBegan and UseEnded and calculate which test an employee
should take based on their HireDate via a query or VBA code...My nature
is to tidy up, but my nature is antithetical to relational database
concepts, I fear...

Another quick question, is there any reason why I'd want to retain
TestID as a pk in tblTest and a fk in tblTestAreas when TestName could
(it seems) be used as pk and fk instead?

Thanks for any more help you can provide. I appreciate it.

 

[Harper]

Hello and thank you for taking the time to give me feedback. I
understand completely what you're saying and agree with its simplicity
and logic.

The thing is, each of these mini-tests are like pieces of one test. So
employees used to take the first four in the list below (Word,
Internet, Spreadsheet, Presentation) as a test called "Computer
Competencies" and now emplolyees take the other mini-tests as part of a
test called "Technology Self-Assessment."

So I think it'd be best to somehow retain the "shape" of the tests by
making clear through the table structures that there are two tests,
with mini-tests or more accurately test areas, that are separately
scored (because then the results tell the supervisor whether the
employees need training in the different areas, Word, PowerPoint,
etc.). Does that seem the correct way to go to you?

I tried out something different in my new reply to Bill. Any further
input you'd have would be greatly appreciated. Thanks and sorry if I
have not been clear.

 

[Bill Edwards]

I would agree with Chad's reply to your previous table structure--it still
was not normalized.

This new table structure, indicated in this post, with the tblTestAreas
table is what you want.

I would be inclined to keep the use began and use ended dates, because what
happens if in the future you add a third set of tests in addition to the two
you already have? You can use these dates to determine the proper tests to
give a particular employee.

I am not sure I like the names of tblTest and tblTestAreas, they seem
counter-intuitive to me -- but that is a personal matter.

My preference is to use autonumber primary keys even when an existing field
seems to meet the criteria for a primary key (for example TestName). What
happens if you want to rename an existing test or if you accidentally
mispell a TestName and then enter in a bunch of related records for it--you
would have to run an update to fix the typo in all the child records instead
of simply fixing it in the parent table. Essentially, if you always use
surrogate keys (i.e. autonumber) cascade updates are never an issue.

 

[Harper]

Thank you both, very much, for your guidance.

Bill, what names would you give the tables other than tblTest and
tblTestAreas? I am also happy to learn more, if you are willing to
share more.

 

[Bill Edwards]

Looking at your description of the problem and the table structure, tlbTest
is the parent table to tblTestAreas. To me these names imply that the
relationship goes the other way. I might call them something like
tblTestGroup (instead of tblTest) and tblTest or tblSubject (instead of
tblTestAreas). The point being I would try and make the name of the tables
reflect the Parent-Child relationship. But again, this is a personal
reading of the names.

 

[Harper]

Actually, that makes a lot of sense to me. Thank you for sharing your
thought process and for your help in general.

 

[mnature]

As I was glancing through "Database Design for Mere Mortals," Chapter 7
Establishing Table Structures, I was reminded of the problem you are having
with multiple tests. You actually have data that represents two distinct
aspects of testing. There are old tests, and new tests.

So, in essence, you have data that all applies towards the testing of
employees. Some of this applies whether the test is old or new, such as the
date the testing takes place. For the old test, this is signified by pass or
fail, and for the new test, it is signified by a score. So, one way of
organizing this data is through the use of three tables, one of which will be
for tests, and two will be for the subsets of tests, which are old tests and
new tests.

tbl_Tests
TestID (PK) [autonumber]
TestNameID
TestDate

tbl_OldTests
TestID (PK) [number]
PassFailGrade

tbl_NewTests
TestID (PK) [number]
TestScore

In your relationships window, connect the TestID of the Tests table to the
TestID's in OldTests and NewTests tables, enforcing referential integrity.

You might think that this is really no better than what you have before, but
the beauty of using this method is that you can then use a filter, based on
HireDate, to decide which employees use the old test, and which use the new
test, and then create a form which brings up either the OldTests table or the
NewTests table. As you create either an old test or new test for an
employee, the TestID of the Tests table will create a new autonumber
automatically.