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)
-- 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:
- 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.
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?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.