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.
mutual exclusive - enforce that with a simple
CHECK constraint. Standard SQL, a bit verbose but fast.
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)
, 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)
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
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
Again, verbose but fast.
There are many other possible approaches:
- A document type like
- Multicolumn FK contraints like here:
- 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.