2

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.

7
  • Maybe you're not considering ways to link a person to a chemical based on a particular job that utilized chemicals? Or this shouldn't be relational but more like a keyword or tag system.
    – JeffO
    Feb 2, 2017 at 19:51
  • A person could use a chemical "directly" or because his/her job(s) require it, they're both acceptable relations with different meaning... What do you mean by "tag system" ? Feb 2, 2017 at 19:53
  • 4
    I know it sounds annoying to have to create N - 1 tables when you add the Nth table, but you could probably automate that with a script. Feb 2, 2017 at 19:59
  • @FrustratedWithFormsDesigner that is a nice idea! Feb 2, 2017 at 20:03
  • 3
    @FrustratedWithFormsDesigner: such a script would be a solution to the wrong problem. The misconception of the OP is that it could make any sense to add all possible link tables between any two entities. This is IMHO completely nonsensical, and supporting the nonsense by a script does not make it less nonsense.
    – Doc Brown
    Feb 3, 2017 at 7:32

5 Answers 5

9

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.

  1. 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.
5
  • 7 is specially true, the proposed model only supports N:M relationships and says nothing at what exactly the relationships are, only "A is related to B" and not "A uses B", "A owns B", "A works for B", etc. As someone says before, it obscures the meaning of the relationships. Feb 2, 2017 at 23:55
  • You seem to be alternatively arguing against OP's solution (points I generally agree with), and arguing against ever using anything that's not a relational database for a data store, which is clearly not defensible. Every now and then a database engineer decides he wants to find a way to make things reusable and support abstraction? Good for him! We need people exploring those possibilities; the current state of most application databases is absolutely abhorrent. Feb 6, 2017 at 6:27
  • 1
    What I am saying is-- use relational database systems for relational databases. I've seen plenty of junior DBEs decide to write "common" libraries, forgetting there are such things as selectivity, query plans, and compilation. The result is the abhorrent mess.
    – John Wu
    Feb 6, 2017 at 9:35
  • @CarlLeth OP is using a relational database. Feb 6, 2017 at 22:20
  • @JohnWu and I have seen plenty of senior DBEs decide to ignore any possible solution that's not completely "by the book", forgetting that the abstractions you can achieve from breaking a rule here and there can provide benefits far beyond the costs. I've seen systems that took your points 1, 2, 3, 6, 8, and 11 as acceptable costs of very valuable abstractions. DBAs are not the only people using and developing the system. Feb 6, 2017 at 23:22
8

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.

15
  • 1
    I know how FKs work, thanks, and I also know that it would break RDBMS enforcing, but I assure you that the domain is well understood, and that all those relations are really needed and meaningfull.. The app already exists, really, and as of now it has to model relationships between about 20-30 different entities... that would be more than 400 tables just for the relationships. Feb 2, 2017 at 20:02
  • 1
    @MatteoTassinari: you wrote "may need to query" - the point is, it is typically a very wrong modeling approach to put every thinkable M:N relationship or relationship constructable by queries into a link table. Leave out the redundant ones whereever possible, then you get a sensible model.
    – Doc Brown
    Feb 3, 2017 at 8:56
  • 2
    @MatteoTassinari: of course, such non-redundant links sometimes occur, and that is where you need to model them, but are you really telling us you have 30 tables with non-redundant M:N relationships between each pair? I suspect your data model is simply not normalized enough.
    – Doc Brown
    Feb 3, 2017 at 12:22
  • 1
    @MatteoTassinari: 30 entities I can easily see. 400 relationships? It's hard to imagine any domain that would require this level of complexity. You're doing something wrong. That said, I have seen systems with the kind of generic key arrangement that you are proposing. The concept does work, but not without compromises. In particular, a database with that many constraints will suffer significant performance problems. Feb 3, 2017 at 15:49
  • 1
    simple-talk.com/opinion/opinion-pieces/bad-carma, for those of you wondering what "that article" is (ninja edit on my part). Feb 3, 2017 at 15:59
7

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.

8
  • "Well understood" as of now: the problem is that what our app is trying to manage is constantly changed based on management/customers requests ... for example, tomorrow we might need to manage Offices and how those are allocated to Persons based on their Jobs, or something along this line Feb 2, 2017 at 20:34
  • I really like the idea of inheritance applied to database tables... never really explored that before. Wouldn't it be quite an impact on performance? Feb 2, 2017 at 20:35
  • @MatteoTassinari there probably is a performance impact, since it requires a join per level of the hierarchy. But that's for the database to worry about. I prefer to have my relations done right, and if the hardware can't cope, buy more better hardware.
    – Mike Nakis
    Feb 2, 2017 at 20:37
  • @MatteoTassinari To begin with a messed up relationship graph, as the question suggests, is a recipe for disaster no matter how fast the hardware is.
    – Mike Nakis
    Feb 2, 2017 at 20:38
  • I'm reading now on how hibernate implements inheritance... why would you define using a discriminator column "lame" ? Feb 2, 2017 at 20:39
5

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.

2
  • 2
    +1 For "will obfuscate the meaning of the relationships". Feb 2, 2017 at 23:43
  • 1
    This is the crux of the argument and why both of OP's solutions are misguided. There is no sensible interpretation of Person_Chemical without more background. A person whats a chemical? A person was exposed to a chemical? A person has training with a chemical? These are completely different relationships that may need different tables with different columns specifying the relationship. Feb 6, 2017 at 6:39
0

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.

2
  • I know about ER, thank you... I'm not sure I understand what you are suggesting. Feb 2, 2017 at 19:57
  • Who is requiring you to link every entity to every other entity directly?
    – JeffO
    Feb 2, 2017 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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