I am having a main table - Client (ClientID Primary Key)
Then 2 tables below:
1. Employee (EmployeeID Primary Key, with ClientID as Foreign Key)
2. User (UserID Primary Key)
Now, it is possible that one employee might be working for more than one client. So, the EmployeeID/EmpCode/Paycode (many other data) will be different for a single person per client. Question is: User should only be able to use ONE user to access both client information. In other words, there should only be ONE user created in the system to access clients for whom employee is working.
Example: "ABC" is a taxi driver (Employee). He is working for "XYZ" and "PQR" (Clients).
1. Client Table:
ClientName: XYZ, ClientID: 1, ...
ClientName: PQR, ClientID: 2, ...
2. Employee Table:
EmpName: ABC, EmpCode: 679, ClientID: 1, Shift: Day, ...
EmpName: ABC, EmpCode: 579, ClientID: 2, Shift: Night, ...
3. User Table:
UserID: 100, UserName: ABC_User
Somehow, I need to normalize the "User" "Employee" table in a way that with only "ABC_User" user login, "ABC" should be able to access data for Client "XYZ" and "PQR". I thought of creating a table "UserAccess" with ClientID, EmployeeID and UserID.
Let me know your thoughts.