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

G

Guest

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
 
G

Guest

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.
 
G

Guest

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

Guest

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.
 
J

Jamie Collins

KARL said:
I think that will work. We are a big organization so have had first name
changes and one sex change.
Click to expand...

I think each should go in its own specific 'history' table (e.g.
EmployeeNames) rather than a generic table (EmployeeDynamicAttibutes).
I think mnature's suggestion would inevitably lead to data being stored
redundantly: you said yourself, sex changes are rare so why store it
every time a name changes?

I think such history tables, in additional to the key column (employee
number) and direct attribute columns (employee names), should have
start date and end date columns. This makes it easy to JOIN to your
calendar table when you need to know what the employee's name was on a
certain date/range (it's also deemed the best way of modelling time,
being a continuum, in the wider SQL world).

Consider that in some countries, human rights legislation allows them
to change their sex on their birth certificates i.e. you may want such
a change to be retrospective in your organization too.

Jamie.

--
 
Top