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.

 

[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