0

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?

  • What is wrong with null values? – Joe W Jan 23 at 13:17
  • @JoeW, 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. – norbertpy Jan 23 at 19:52
  • Your preferred RDBMS and version? How many distinct from sources? How can those change over time? Do you want / need to enforce referential integrity? Roughly how many rows? Concurrent write operations from multiple users? Most important goals? performance / size / reliability / portability / simplicity? – Erwin Brandstetter Jan 23 at 20:18
  • @ErwinBrandstetter, postgres 9.6. For now 4 distinct from but can grow to 10-20. They change rarely. Without referential integrity it would've been easy of course. Roughly 200K-500K. Concurrent write is needed. portability and simplicity is more important than performance and size. – norbertpy Jan 24 at 0:02
  • I suggest you edit the question to show these essential preferences / requirements prominently. – Erwin Brandstetter Jan 24 at 2:15
2
+100

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?

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:

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:

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.

  • Very thorough answer as usual. Thanks Erwin. – norbertpy Jan 24 at 5:19
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
2

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    |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.