Compromise between formal correctness and convenience in database relations

Question

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.

Answer 1

Please don't

Theory

The word "relation" (as in relational database) has a very specific meaning. It is not about relationships. It is a term from set theory that defines what a query result is-- a cross product among all the tables of interest, subject to a constraint (a series of predicates derived from your table joins). A relation is a constraint. All of the computer science behind database algorithms come from properties of relations and constraints. If your manner of thinking differs from those scientists, you are ignoring the science.

Bottom line: constraints are very important.

Every now and then a clever database engineer decides that he wants to be more like a c++ developer. He wants things re-usable, generic, similar in pattern. This sort of thinking gives rise to patterns like EAV where any entity can be stored in a single generic structure. Well, EAV is a widely hated design and causes all sorts of problems... if you've ever used Magento, for example, all that EAV stuff wreaks havoc on the system's performance and the only band-aid is to offload a job which generates indexes by flattening out the EAV.

Don't be him. Embrace the constraints.

Constraints make query results possible. Constraints make referential integrity possible. Knowledge of how the constraints work make possible all sorts of performance optimizations. Your database engine was built by computer scientists intimately familiar with this body of mathematics.

If you deviate from this philosophy, you will be unwittingly be stepping off the reservation, and you will probably get yourself into a mess long before you have realized it.

Practice

Just off the top of my head here are somethings that will hurt you later if you insist on this generic M:N model.

1. You will be forced to use the same data type for all primary keys.

2. You will be unable to use natural keys, which are sometimes superior (e.g. if you need to search a range of records that could be identified by a range of natural keys).

3. You are limited in terms of namespace ownership (for example, if you have picked an int for your PK, you won't be able to use GUIDs for tables that contain rows that may come from different data sources).

4. You will run into problems with transactional replication if there is more than one publisher (keys on each publisher require their own namespace).

5. You will be unable to use composite keys even if they are more appropriate in some cases.

6. You will be unable to enforce any R/I, unless you use triggers, which won't perform as well.

7. The design does not naturally enforce any relationship other than M:M; there is no accommodation for limited something to 1:1 or 1:M. It would have to be enforced by application code, or perhaps a trigger.

8. You will be unable to use anything like cascading deletes.

9. You will develop massive hotspots and contentious locks on your single M:N table.

10. You will be unable to define attributed relationships, i.e. if you need to record information about the relationship between a person and a machine ("can use","is prohibited","is certified","is a trainer") you have nowhere to put it and nowhere sensible to add it.

P.S.

11. A business analyst examining your schema for insight into the business rules will have a tough time of it. Relationships could only be understood by diving into code, which is much more tricky.

Answer 2

First, I'd like to point something out: If you feel compeled to make a N:M relationship between any possible two entities permutation, it could mean you are not studying the domain problem well. I've never seen a case when you need a relationship (N:M or otherwise) between any possible entity and any other possible entity.

Now going to your Relations tables (shouldn't it be Relationships?).

• You cannot have two FK on the same column pointing to two or more different "parent" tables.
• A FK can only point to one parent table.
• Your solution would require enforcing everything in code meaning you will not have the RDBMS enforce referential integrity for you.
• You will have to store the entity name as a string, more code-based integrity checking (error prone).
• You will not be able to query your database with a graphical query builder if you wanted.
• The data will not be of value outside "the app".

For me that solution is a no-go. My recommendation is that you study the domain problem harder and you will find out that you don't need a relationship for every possible two entities combination.

Answer 3

Judging by the peculiarity of your model, where every single entity has a relation with every single other entity, and looking at your comment to Tulains Córdova's excellent answer, I am led to believe that you have an exceptionally strange model in your hands.

Lets begin from this: you say that the domain is well understood, but you are also concerned with what happens as you keep adding entities. These two statements are at odds with each other: either your domain is well understood, in which case you already have pretty much all of the entities and relations among them that you were going to have, or you don't.

What I suspect is happening is that your entities are highly isomorphic to each other, to the point where they are not in fact separate entities. They all belong to the same basic entity, (let's call it "Entity" with capital "E",) and the fact that one Entity is a Person while another Entity is a Machine and so on is just an attribute of the Entity. If that is in fact the case, then the best solution for you might in fact be to have a single "Entity" table, and a single many-to-many relation table where any entity can have a relation with any number of other entities in the same table.

This would be a sound approach as far as relational database theory is concerned, and perfectly workable from the point of view of referential integrity. It would also perform much better than your monstrous "Relations" table joining disparate tables.

If your entities are not entirely isomorphic to each other, so they need to have some different columns each, (do they really?) then you can employ inheritance the way ORMs do. For details about this you can read up on how hibernate implements inheritance, (look at the "table per class" strategy, skip the "table per hierarchy" strategy and the lame notion of "discriminator column",) but to give you a quick idea of what it is about, your schema could look like this:

Table Entity

    entity_id, entity_column2, entity_column3... person_entity_id, machine_entity_id

Table ManyEntitiesToManyEntities

    left_entity_id, right_entity_id

Table PersonEntity

    entity_id, person_column2, person_column3...

Table MachineEntity

    entity_id, machine_column2, machine_column3...

...and so on.

So, if you were to add a person to your database, you would begin by issuing an id (preferably using a sequence, to save a roundtrip to the database) say 10, and then you would be adding one new row to the Entity table, where person_entity_id would be 10 and machine_entity_id would be NULL, since this entity row corresponds to a person, not a machine. Then, you would also be adding a row to the person table, with an entity_id of also 10.

One thing is clear: the "Relations" table as suggested by your question is a no-go because it completely destroys referential integrity checking.

Answer 4

Your example is IMHO unrealistic, since if the model reflects any kind of real world data, it does not make sense to model each possible M:N relationship explicitly. Whilst in a small model with 4 tables this might happen, in a bigger model with 30 tables it is very unlikely that this approach makes any sense.

Well designed relational models should try to achieve as few redundancy as possible (except in rare cases, for optimization issues). So if one M:N relationship is just a consequence of another relationship (like "person X is related to machine Y because there is a person-job and a job-machine relationship), you should avoid to model the unnecessary, redundant relationship between persons and machines. Of course, there might be sometimes a machine-person relationship which is not redundant to the first two ones, and then it might make sense to model it explicitly, but to my experience it is extremly unlikely this is the situation between every pair of your 30 tables.

However, to judge if a relationship is a redundant one, you need to know the it's actual domain meaning. For example, a relationship Person_Job is meant to express a business meaning, it could be that a person

• is assigned to a job (maybe by a contract)

• is qualified for the job (but is not necessarily assigned to it)

• applies for the job

You could even want to model all of these meanings, which could end up by having more then one link table between the same pair of tables - which would IMHO be fine as long as your system contains use cases for all these different kind of relationships.

In my experience, when you restrict your link tables to the ones you actually need to model the use cases in your system, and avoid to model any redundant link tables, you do not get a combinatorial explosion of tables.

Naming your link tables like Person_IsQualifiedTo_Job expresses the meaning and gives you a model which is easy to understand. A name like Person_Job does not reflect that and it is harder to understand what kind of relationship is meant. And using a generic Relations table will fully obfuscate the meaning of the relationships, so this will become hard to maintain and extend, even if it looks convenient to you at a first glance.

Answer 5

You may want to consider entity relationships. You'll have flexibility (assuming that's the whole purpose), but performance is going to suffer because of it.

I think this requirement is more of a like to have instead of a must have. Personally, I would dig deeper and push back a little to see how this works in the real world.