Typically you would design the database such that someone coming into the database alone could get a handle on how the application worked. Had they seen such a foreign key they may not be aware of it's implications.
You could approach this in two ways:
First, you could do as you mentioned. Create two separate tables and handle them there.
Second, and the way I would recommend, you would create a table to handle all the common fields between the two, and then two other tables, one for internal-specific data, and one for external-specific data. Each of those tables would have a primary-foreign key to the common table. I.e. a Guid or ID.
The downfall of the second method is the application would be required to be aware of the two different tables, and depending on the method you are using to implement it (.NET, PHP, Python, etc) you may find one method easier than the other.
I personally use .NET and C# or VB, depending on the project, and in this situation I would go with the three-table design, as I would create an abstract class ("Event") and then two classes to inherit it and all the common properties ("ExternalEvent", "InternalEvent"). The Event class would handle the interaction of the shared properties, and the InternalEvent and ExternalEvent would handle the logic specific to them.
The upside to this approach is the polymorphism of it. If you add a common field to the two in the future (for whatever unprecedented reason) you only have to add/change it in one location.
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.
Best Answer
The attributes should be split in 2 tables because the configuration details are not depending on the user but on the configuration of an external system. If a user can have only 1 configuration then you must use an 1-1 relation. If a user can have multiple configurations then you must use the 1-M relation.
This is the theory. If you want (in case of a 1-1 relation) put all in 1 table than this is also 'allowed'. Remember well that if one day the relation becomes 1-M that you are in problems. This is also why it is preferred to split the information into 2 tables.