many to many table design

[Souris]

I have an employee table which has unique employee ID as primary key.
I need deal with another table and infromation using Login ID which the
system re use the login ID after employees left

There are some solutions for this, but I am not sure which one is the best.

1. Create a middle table like Employee ID and Emp State to link 2 tables
2. Have a composite key in Login Table Employee ID, and Login ID only
employee ID and Login Id composite key are unique.
3. Just use Login ID as primary key table and delete employee records after
employee left.



Any other solutions?
Your information is great appreciated,

 

[rpw]

For what it is worth:

Your option number 3 seems to be a very WRONG thing to do.

Option number 2: Makes perfect sense to me BUT I might be misunderstanding
something - is "Login Table" a "middle" table? If so, then yes #2. If not,
then create a middle table with EmpID and LoginID as composite PK.

Option number 1: I'm sorry, but I don't understand how Emp State works into
the problem.

 

[Souris]

Emp Stat is a field to identify status of employee.
Because Login ID is unique when employees still with us which is a kind of
dilter out all unique login ID current uses.

I think number 2 is best choice.

Thanks again,