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

database-designerdtable

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:

Entities

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.

Best Answer

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.

Related Question