Normalization Help needed

[Wulf13]

I currently have three tables.

The first table contains Employee ID, and Name of Employee

Second table contains Employee ID, and an autonumber

The third table contains an autonumber and a unique PIN

The 1st/2nd table are connected by Employee ID, the 2nd/3rd table connected by the autonumber. Now not every Employee gets the unique pin and the 3rd table gets re-done every year. This structure works great for all "current" employees. Employees that leave get deleted out of the 1st table, thus causing them to get deleted out of the second table due to relationship. This becomes a problem because I need to keep a record of who had what unique PIN, regardless of who is still assigned one or not. Anybody have any ideas on how to do this.

 

[RV]

Why using three tables as you only need one?
And why would you delete employee data?

Here's the columns I'd suggest;

EmployeeID (auto number)
EmployeeName
PIN
StartDate
EndDate

If it happens or could happen that an Employee is re hired, you'd like to move the StartDate and EndDate to a seperate table to keep track of an Employees record.

RV

 

[Wulf13]

I tried that structure at first.

The unique pin column changes in the third table on a random periodic basis. I would join the first two tables into one but not every employee is assigned a unique pin. The reason I would need to delete the employee data is if they no longer worked for us. If they quit and then came back, they would need to be assigned a new PIN.

 

[Pat Hartman]

If you need to keep track of who had what pin and when, you cannot delete employee records. You need two tables.
tblEmployee:
EmployeeID
HireDate
TerminationDate
LastName
etc.

The termination date will allow you to identify current employees. They will all have non-null termination dates.

tblEmployeePIN:
EmployeeID (Primary key - fld1; foreign key to tblEmployee)
StartDate (Primary key - fld2; unique index fld1)
PIN (unique index fld2)
EndDate

The overlapping primary key and unique index are to prevent an employee from having more than one PIN at a time and to prevent the same PIN from being assigned to more than one employee at a time.

You need to define the relationship to enforce RI but NOT allow cascading updates or deletes. You do NOT want to allow employees with PIN history to be deleted.

 

[Wulf13]

I see what your talking about Mr. Hartman, as a matter of fact I just ran across a similiar post after many hours of searching and you happen to provide the same answer there. This should solve my problem, thank you very much