How to alias employee codes on change and keep old records.

[KARL DEWEY]

I need to setup a database to track occupational exams. Employees change
Departments, EmployeeCode, names, etc so I need a table to translate EMP# to
Alias#. This is necessary to reflect the current name, Department,
EmployeeCode but still retain the information as it was when it occurred.

How do I relate an EMP# to all of their Aliases?

tblEmployee –
EMP# -Autonumber – primary
EmployeeCode
LName
FName
MI
Sex – M/F
DOB DateTime
Inactive – Yes/No
InactiveDate
InactiveReason

tblAlias –
EMP# - number integer foreign key
Alias - number integer foreign key

tblEMP_Exam –
Alias - number integer foreign key
Exam# - number integer foreign key
ExamDate
Re-occur – Yes/No
Remarks - Memo

tblExam –
Exam# - Autonumber primary
Type
Description
Interval – number integer

 

[mnature]

Interesting problem. My thought is that you should only include fields in
the Employee table that you are very sure will not change, and then have a
table for Aliases that includes all fields that might change. Then use the
Alias as your foreign key for the exams. Might look something like this:

tbl_Employees
EmployeeID (PK)
NonChangingInfo (variety of stuff here, first name, gender, DOB)

tbl_Aliases
AliasID (PK)
EmployeeID (FK)
ChangingInfo (such as last name, Dept, Emp Code)
ChangeDate (so you can chronologically track when a person's alias changes)

tbl_Exams
ExamID (PK)
ExamInfo

tbl_EmployeeExams
EmployeeExamID (PK)
AliasID (FK)
ExamID (FK)
EmployeeExamInfo

Then you can look up an employee through all of their aliases. This may not
be the best structure, but is just a thought of how to do it.

 

[KARL DEWEY]

I think that will work. We are a big organization so have had first name
changes and one sex change.

 

[mnature]

I suppose that only leaves date of birth as unchanging . . .

By the way, remember to protect this type of information. You don't want to
make the evening news for allowing identity theft.