Model multiple 1-m relationship to a single entity

Question

I have an entity called Box. This box has a from property which could be from a variety of different entities. It could be from a user, office, import, storage and many more.

So far I can think of two ways to model this:

Approach one:

| box_id | box_content | from_user | from_office | from_import | from_storage | ...  |
|--------|-------------|-----------|-------------|-------------|--------------|------|
| box_1  | apple       | user_a    | null        | null        | null         | null |
| box_2  | orange      | null      | null        | import_b    | null         | null |

Approach 2:

BOX
| box_id | box_content |
|--------|-------------|
| box_1  | apple       |
| box_2  | orange      |

USER
| user_id | name  |
|---------|-------|
| user_a  | Rick  |
| user_b  | David |

USER <-> BOX
| user_id | box_id |
|---------|--------|
| user_a  | box_1  |

IMPORT <-> BOX
| import_id | box_id |
|-----------|--------|
| import_a  | box_2  |

But I don't like either ways because in approach 1 there are so many null columns and in approach 2 I need to create a new relationship table every time there is a new from entity. Is there a better way to model this in a relational database?

Answer 1

To sum up your added comment: Preferred RDBMS: Postgres 9.6, enforce referential integrity even under concurrent write load, at most 20 different sources, 500k rows, keep it simple.

Referential integrity is the hard part. The one rock solid way is with foreign key constraints. But you want to refer to one of multiple tables. From the same column?

• How to have a foreign key pointing to two primary keys?

Short answer: not possible.

That leaves no other choice than either have one column for each source (your approach 1) or one table (your approach 2). Tables would make sense for a n:m relationship, not so much for 1:n. Your approach 2 is too complex, wastes too much storage, and it does not even match your data model to begin with.

Leaves us with approach 1. You worry:

the problem is they are mutually exclusive. Which means there is only one column with actual value at any point in time and the rest of the columns are all null.

1. mutual exclusive - enforce that with a simple CHECK constraint. Standard SQL, a bit verbose but fast.

2. NULL storage - is extremely cheap in Postgres. Basically, 1 bit in the NULL bitmap. Details:

   • Do nullable columns occupy additional space in PostgreSQL?

So I would do this (Postgres implementation):

CREATE TABLE box (
   box_id      serial PRIMARY KEY
 , box_content text -- NOT NULL?
 , from_user   int  REFERENCES users(user_id)
 , from_office int  REFERENCES office(office_id)
 , from_import int  REFERENCES import(import_id)
  -- more?
 , CONSTRAINT exactly_one_from
   CHECK (CASE WHEN from_user   IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_office IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_import IS NULL THEN 0 ELSE 1 END = 1)
);

The CHECK constraints enforces exactly 1 reference, the FOREIGN KEY constraints enforce referential integrity under all circumstances.

If the many NULL columns are a burden to the human eye, consider a VIEW like:

CREATE VIEW box_view AS
SELECT box_id, box_content
     , COALESCE( from_user, from_office, from_import) AS from_id
     , CASE WHEN from_user   IS NOT NULL THEN 'user'
            WHEN from_office IS NOT NULL THEN 'office'
            WHEN from_import IS NOT NULL THEN 'import' END AS from_type
FROM   box;

Again, verbose but fast.

db<>fiddle here

There are many other possible approaches:

• A document type like hstore or jsonb.
• Multicolumn FK contraints like here:
  • Enforcing constraints "two tables away"
• Enforce referential integrity with trigger instead of FK constraint.

But none of these guarantee referential integrity under concurrent write load like this approach does. Good performance and the smallest disk footprint are additional pros.
The downside: you have to alter the table definition (and view, queries, ...) for every additional "from" type.

Answer 2

My option would be

BOX(ID,descr)
USER(ID,....)
OFFICE(ID,...)
IMPORT(ID,...)
FROM(FROM_ID, office_id, user_id, import_id) FK ....
RELATION(FROM_ID, BOX_ID) fk FROM_ID references FROM, BOX_ID ref. BOX

To keep tables as clean as possible and not to have 'too many' little tables

EDIT: You may choose to adopt 'talking codes' for from_id:

U123 if from user
O123 if from office
I123 if from import

Answer 3

I would suggest a third approach where you just note which source type and id is for each box.

| box_id | box_content | box_source | source    |
|--------|-------------|------------|-----------|
| box_1  | apple       | user       | user_a    |
| box_2  | orange      | office     | office_a  |
| box_3  | banana      | import     | import_a  |
| box_4  | pear        | storage    | storage_a |

| box_source |
|------------|
| user       |
| office     |
| import     |
| storage    |