In a one-to-one relationship, where should the foreign key be placed?

Question

I am currently designing an ERD for a project management system. I am unable to choose between two workable solutions as the best solution as I wish to embrace the best standards.

The two entities that can access the system are the clients and employees. Since they have different information, I decided to treat them as different entities. I figured that it is best to have a single table for users of the system rather having the username and password fields be found in both tables of clients and employees. Plus, the constraints I have to follow:

• All users are only limited to employees and clients.
• But not all employees are required to be users of the system.

Here are the following entities without relationship yet defined:

Now, there is only one-to-one relationship between users and clients, employees and users. My question is where should I place the foreign key? Should I place two foreign keys in users table namely employee_id and client_id which makes it easier to find the associated client and employee entity from the user entity and where I can define authorization rules more easily? Or should I place each foreign key on clients and employees referencing the users table since I am unsure if it is an acceptable practice to have two foreign keys in a table in which only one foreign key can be used at a time.

Answer 1

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:

1. The sub-type/superset solution, as pointed out by @Joel Brown.
2. 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.
3. 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.

Answer 2

It is acceptable to have mutually exclusive attributes in a table. If you only have one pair of such attributes, this may be the most practical solution.

However, some people may look at your situation as a sub-typing issue. In this view, you are missing the superset entity. You have a collection of (something), some of which are employees and some of which are customers and some of which are also users.

Have you also considered what happens if an employee also happens to be a customer? Is that important to your system or is that something that would be considered coincidental?

If you think its important to treat all users as a feature of one type of thing, then you need to implement the (something) which is currently missing from your table definitions.

Consider the following ERD:

If you supply the missing superset entity, then every employee is one of these, as is every customer, and also therefore every user. The hard part of this is coming up with a reasonable name for this superset, but it solves the issue of having to figure out which foreign key to follow from user. It also makes a connection between employee and customer should one happen to exist, and should that connection be of interest.

Answer 3

It may be useful to view your case as two problems, rather than one. The first problem is a class-subclass (or, if you prefer, type-subtype) problem. Employees and Clients are clearly subclasses of some superclass that you have left unnamed. I'll call them Persons. (See the subtypes tag).

The second problem is the one-to-one relationship between Persons and Users. If you just had a Persons table, then the question of which way the foreign keys should go is driven by the fact that the relationship is mandatory at one end, but optional at the other end. If you put PersonID in the Users table, you won't have to use any NULLs here. Every user is a person.

Now back to the first problem, the class-subclass problem. For reasons that seem to make sense, you've chosen to have two separate tables, one for Employees and one for Clients. If you add the Persons table, as I've suggested, you may find it convenient to store some common attributes, like name and phone number, in the Persons table instead of in both of the subclass tables.

This technique has a name: it's called "class table inheritance" in the literature. You can search for it on the web, or over in SO.

There is a second technique, called "shared primary key" that can be useful in cases like this one. Using shared primary key, the Employees table and the Clients table do not have an independent ID field. Instead, the PersonID field is declared as the primary key to the Employees or Clients table, in addition to its role as an FK referencing the Persons table. This technique is common when implementing IS-A relationships.

This involves a little extra programming when you insert a new Employee or Client, but it may well be worth it.

Now, you can join the users table to the Persons table, or the Employees table, or the Clients table, as the situation requires. Only the category of users you want will appear in the joint result.

Answer 4

I've only quickly looked at this but my first thought is to utilise NULL attributes in your Users table for EmployeeId and ClientId. They can then be populated as required and defined as Foreign Keys.

UserName is the Primary Key for the Users table.

Use the UNIQUE attribute on the EmployeeId, ClientId and the composite EmployeeId+ClientId within the Users table.

This would address the "Employee is also a Client" issue in the Users table.