1

I'm developing a database where I will have two types of users: Employee and Client. I'm thinking it would be ideal to just have one big USERS TABLE with a user_ID as PK and Type column to distinguish whether the user is an Employee or a Client. This is to make it more convenient when a user logs-in where it will only check one table: USERS TABLE for username and password

However, I realized that this means I would be losing the opportunity to have separate ID's as PK for Employees and Clients (employee_ID and client_ID).

Would having another table called EMPLOYEE TABLE and CLIENT TABLE (that have user_ID_FK as FK, employee_ID or client_ID respectively) be ideal or would that de-normalize the tables?

How best to handle user log-in where there are multiple different types of users (Employee, Client, etc.)?

  • Is there data related to an employee or a client that is unique to that category? (example: you might need an emergency contact for an employee, and not need one for a customer). If there's data unique to the type of person, then having tables that contain the columns that only pertain to one type of user is reasonable. Also: can an employee also be a client? Should they have separate accounts if that's true? Again separate tables make sense; in fact, in that case, you may want to rethink having a user table; as you need to ensure that each user is tied to one and only one user category. – RDFozz Aug 29 '18 at 16:33

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.