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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.