Categorisation in database models

Question

I have a relational database, in which I want to have a table 'Entity'. This entity has a type: it's either a Person, a Company or a NonProfit. Depending on this type, it should have different properties (columns):

Person:
- id
- name
- age
- statuses: ['child', 'adult', 'senior']


Company:
- id
- name
- address
- contact_form
- industry
- statuses: ['startup', 'scaleup', 'corporate']


NonProfit:
- id
- name
- address
- bank_account
- statuses: ['environmental', 'social']

They all have a name, and in the front-end they should appear in a similar fashion in a list:

Entities: 

Name          Type        Status
----------------------------------
Mary          Person      Adult
Mcenroe       Company     Startup
Joe           Person      Child
BetterWorld   NonProfit   Social

If you then click on details, depending on the type, the entity is displayed in a different fashion.

I'm about to create 4 different tables: Entity, Person, Company and NonProfit, and then refer to either one of the latter three in the Entity table.

Entity:
- id
- person_id #optional
- company_id #optional
- nonprofit_id #optional

Then somehow enforce that the entity has only one of the three foreign keys. But it feels clunky, so I think there is a better way. I'm not too experiences with relational databases though.

Whats the best way to enable this kind of data in a relational database?

This question may have an answer [here](https://dba.stackexchange.com/questions/193394/is-it-a-bad-practice-to-have-several-mutually-exclusive-one-to-one-relationships/193401#193401).. — Walter Mitty, Jul 25 '20 at 16:34
These are both super valuable in understanding my problem. Needed to know terminology, inheritance and exclusive one-to-one relationships as described in your links are terms that came to mind already, but wasn't sure how to look for it. Thanks @philipxy and WalterMitty — Sventies, Jul 26 '20 at 09:29

Gordon Linoff · Accepted Answer · 2020-07-25T13:57:27.387

You may be better off with a single table and check constraints:

create table entities (
    entityid . . .,
    name varchar(255),
    type varchar(255),
    status varchar(255),
    age int,
    . . .  -- remaining columns
    check (type in ('Person', 'Company', 'Nonprofit'))
    check (type = 'Person' and status in ('child', 'adult', 'senior') or
           type = 'Company' and status in ('startup', 'scaleup', 'corporate') or
           type = 'Nonprofit' and status in ('environmental', 'social')
          ),
    check (age is null or type = 'Person'),
    . . .  -- remaining check constraints
);

SQL is not great at these "one-of" relationships. What this buys you is the ability to have foreign key relationships to the entity, regardless of type.

If you split the data into three separate tables, you lose the ability to have such foreign key relationships.

An alternative that is sort of the best of both worlds is four tables, an entities table and three additional tables, one for each type. The additional tables and entity table can share the same primary key. In this case, though, you have some key overlapping columns that are type specific, so even this will be a bit messy. If you store the statuses, for instance, in the type tables, your entity query would look something like this:

select e.*, coalesce(p.status, c.status, e.status)
from entities e left join
     persons p
     on e.entity_id = p.person_id left join
     companies c
     on e.entity_id = c.company_id left join
     nonprofits n
     on e.entity_id = e.nonprofit_id;

That seems a bit messy, comparable to all those check constraints.

score 1 · Answer 2 · answered Jul 25 '20 at 14:04

If the ask is to have a common entity which displays data from the three entities, then how about a view which combines the data from all of the three entities-> Person/NonProfit/Company as follows. I am assuming there isnt any relationship any relationship between a Person and a Company, other than the fact that these are just two entities in your data model.

create view all_entites 
 as
 select name,'Person' as entity,status
   from Person
 union all
 select name,'Company' as entity,status
   from Company     
  union all
 select name,'NonProfit' as entity,status
   from NonProfit

Categorisation in database models

2 Answers2