How to model a relationship with variable tables (polymorphism)?

Question

What is the best way to model the following kind relationship?

Let's say I have tables representing two types of entities: company and person. I have a customer table with an entity_id field, which should refer to either a company or a person.

If I simply store a company_id or a person_id in entity_id then I won't know which table to look up the entity in. The first solution which has occurred to me is to have a entity_type field in customer which would take the values "company" or "person" to tell me which table my foreign key in entity_id refers to. I'm not sure if this is the best way though.

This seems like it should be a fairly common problem in database design, so I'm hoping there is a design pattern I can adopt.

I've used the word polymorphism in the title in the broader sense, so I'm not referring to inheritance. In my example, a company is not a person (or vice versa) but they are still interchangeable. Other questions I've found on Stack Exchange sites have assumed inheritance is also a requirement, so I'm not sure that there is a suitable duplicate.

Answer 1

There seem to be 2 main ways to model such a situation:

(1) Use supertype/subtypes. However, as we are not discussing "object oriented" design, the second option may be a bit more appropriate.

(2) Use a so-called ARC, which expresses a XOR relationship.

Suppose we have an entity TRANSACTION. Each customer can be involved in one ore more transactions AND a customer is either a COMPANY or a PERSON (XOR). Our ERD/relational model could look something like the ones below. Notice the "arc", that is drawn across both relationships.

As for the implementation: you can code a CHECK constraint (in order to avoid triggers), which will enforce the XOR (exclusive or). The complete DDL code looks something like this (notice the CHECK):

CREATE TABLE company ( 
  c_id NUMBER PRIMARY KEY
);

CREATE TABLE person ( 
  p_id NUMBER PRIMARY KEY
);

CREATE TABLE transaction ( 
  t_id NUMBER PRIMARY KEY
, c_id NUMBER 
, p_id NUMBER
);

ALTER TABLE transaction 
ADD CONSTRAINT fkarc CHECK (
     ( (c_id IS NOT NULL) AND (p_id IS NULL) ) 
  OR ( (p_id IS NOT NULL) AND (c_id IS NULL) ) 
);

ALTER TABLE transaction
ADD CONSTRAINT transaction_company_fk 
FOREIGN KEY ( c_id ) REFERENCES COMPANY ( c_id ) ;

ALTER TABLE transaction
ADD CONSTRAINT transaction_person_fk
FOREIGN KEY ( p_id ) REFERENCES PERSON ( p_id ) ;

(Tested on Oracle 12c)

Answer 2

I know you said you are not referring to inheritance, but the best literature concerning your case all refer to inheritance in describing the problem, and proposing a solution.

There are a handful of websites that give detailed solutions on how to design relational tables that express the type/subtype or the class/subclass relationship. The most important thing is to use the right buzzwords in your search.

In Entity-Relationship modeling between customers, companies, and persons is called "generalization/specialization". A search on this will give you lots of good material. Companies are specialized customers, and so are persons. another useful buzzword is "IS-A relationships".

In relational modeling, there are three techniques that have been widely shared for covering this kind of problem. Here they are "single table inheritance", "class table inheritance", and "shared primary key". I know the word inheritance appears there, but trust me, a search on these items is going to give you some articles that address your specific case, although they may be talking about autos, trucks, and vehicles or dogs, cats, and pets instead of companies, persons, and customers.

Some of the hits you will get will lead you right back here, to stackexchange.dba. Others will lead you over to Stackoverflow, where there are three tags to collect information about the three techniques.

The only thing not covered in detail is how you add constraints to enforce the mutual exclusion rule, what you have called XOR. If you use shared primary key, the constraint for mutual exclusion will be much like what you have already presented, only simpler.

To summarize what all this means in your case:

You will have four tables: transaction, customer, person, and company.

Transaction will contain a customer_id field, which references the id field in customer. Customer will have an id field, probably filled in by an autonumber feature, as is commonly done. Person does not have an id field. Instead, it has a company_id field that acts as both a primary key and a foreign key referencing company.id. Company is treated the same way as person, with a company_id field that is both a PK and an FK.

The customer table can contain fields, if any, that pertain to both companies and persons.

Finally, you will need a constraint in the person table that says that customer_id is not in the company table, and one in the company table that says that customer_id is not in the person table.