Suppose that you are modeling and application in which you'll have 4 kinds of entities, say
- a
Person
- a
Job
- a
Machine
- a
Chemical
Now you have to model all possible relations (N:M) between those entities, because your business logic is such that it really does make sense, so you end up having many tables:
Person_Job (person_id, job_id)
Person_Machine (person_id, machine_id)
Person_Chemical (person_id, chemical_id)
Job_Machine (job_id, machine_id)
Job_Chemical (job_id, chemical_id)
Machine_Chemical (machine_id, chemical_id)
That's (4 x 3) / 2 = 6
tables.
Now, if another entity steps in, they would become (5 x 4) / 2 = 10
tables; and with another one (6 x 5) / 2 = 15
tables, and so on, every time adding another N - 1
tables.
Would it still be correct to instead implement just one additional?
Relations (from_entity, from_id, to_entity, to_id)
In this way, it looks like to me that it is less correct from a formal point of view, but with only two tables you can model any kind of relationship in the system.
At what number of entities and relationships could this last form become preferable over the first?
Please do note that this is just a made-up example to clarify the problem at hand: the real domain would count many entities, about 30.
EDIT: I think we can still have RDBMS foreign key integrity with some additional tables, for example
EntityType (
entity_type varchar primary key
)
Entity (
entity_uuid uuid primary key,
entity_type varchar references EntityType(entity_type),
)
Relations (
from_uuid uuid references Entity(entity_uuid),
to_uuid uuid references Entity(entity_uuid),
primary key (from_uuid, to_uuid)
)
Job (
job_uuid references Entity(entity_uuid),
job_name varchar,
[...]
)
Person (
person_uuid references Entity(entity_uuid),
person_first_name varchar,
person_last_name varchar,
[...]
)
In such scenario, you first insert the row in the Entity
table and then in the specific table; at the same way deletions would be made on Entity
table and the various FKs could do a CASCADE DELETE to keep data consistent.
N - 1
tables when you add the Nth table, but you could probably automate that with a script.