First of all, let me just say (as others have pointed out), that there is nothing wrong with having mutually-exclusive foreign keys!
Second, despite it's elegance and applicability to other scenarios, I don't think the superset solution posted by @Joel Brown is appropriate for your exact case.
Although it makes it much easier to deal with sub-types of 'people' (entities, users, what-have-you), it doesn't sound like your project will ever expand beyond those two groups.
As I see it, there are three techniques you can employ here:
- The sub-type/superset solution, as pointed out by @Joel Brown.
- Using the three tables you currently have, with either two FK's in
users
, or an FK in each of the Clients
and employees
tables.
- Having a
Role
and user_role
table, to employ user roles from the users
table (and then demoting 'client' and 'employee' to roles).
Each of these options (as you might imagine) has it's own strengths and weaknesses.
Supersets:
Pros
- Allows you to add any number of "types", each of which can have as many different fields as it needs.
- Permits one 'user' to be both a customer and an employee (or any other sub-type) simultaneously.
Cons
- Does not (inherently) permit any sort of exclusivity between the sub-types. i.e. if we have sub-types 'Cop' and 'robber', one entity can be both a Cop and a Robber at the same time.
- Therefore (as you mentioned in your comments), makes it difficult to determine what type a particular person/entity is.
- Does not allow there to be an employee whom is not also an 'entity' (or person, or what-ever name you give the master table). (although, I should point out , does allow an employee whom is not a user.)
Current Design
Pros
- Allows each of your "types" (
Clients
and employees
) to have as many different fields as it needs.
- Permits exclusivity between types, i.e. one user cannot have a
user_type
that is simultaneously Client
and user
.
- Allows there to be an employee whom is not also a user.
Cons
- Will not scale to more than a handful of "types". i.e. if you had more than just
Clients
and employees
, but 20+ types - queries on this would get very slow (as each type needs a new FK in users).
- Does not implicitly allow you to determine what 'type' a
user
is easily (assuming you stay with FK in users
, PK would inverse this).
User Roles
Pros
- Can scale to thousands (or milions?) of roles (as roles are a much simpler construct, not requiring their own table each).
Cons
- Would not allow each role to have as many different fields as it needs. (Roles would all have the same fields, most likely nothing more than a name - everything would be in application logic to decide what a role means.)
- Does not permit any sort of exclusivity between roles. (You can be a Cop and a Robber at the same time again.)
So what's the answer?
Well, Roles allow massive amounts of scalability, but this comes at the cost of modelable complexity. Due to their simplicity, you can have a thousand roles, but each role will tell you very little (since a role cannot have fields of it's own). Therefore, I would say roles are inappropriate for your project, as you need to model different things for employees versus users.
Sub-types (a.k.a. superset) might work for your project. However, the added difficulty in querying to determine a users type probably isn't worth the benefit for a system that will only ever have two types. Additionally, the case of an employee also being a customer would probably never happen. It would be pretty safe to assume someone working internally and for a customer (if such a thing were even permitted by all involved), would have a separate email/logon/identity/username for each of their roles (positions) and hence would just login on the appropriate account.
Your current design (with the double FK in users
) both permits the modelling of the fields you need for each different type, and allows you to have the (desirable?) mutual exclusivity to prevent a user from being both a customer and an employee. It does however maintain the issue that it is difficult to know (from just a users
id
) which table to query for that user (i.e. is the user a customer or employee).
To solve that problem (if you particularly care), I would suggest adding a field to users
to signify the type. This could literally be something like user_kind
, with an enum(client,employee)
type. If you want to get even fancier, you could extract both that field and user_type
to a fourth table, which has an FK back to users
and the same dual-FK (on user_type
) to Clients
and employees
.