This question has already been answered:

There are 2 pairs of tables.

Some reference:

  • A (id)
  • A_Component (id, a_id)

And any sample from this directory:

  • B (id, a_id)
  • B_component (id, b_id, ac_id)

UML

Obviously, this structure is locked into a rhombus, where the database does not ensure data integrity - nothing forces the B.a_id and A_Component.a_id match, but we would like.

Once again: it is necessary that B_C.b_id and B_C.ac_id point to the same entity A

I see a couple of possible solutions:

  • exclude table B , thereby breaking the rhombus dependencies.
    the solution does not fit, because in B there is more data related to the entire sample
  • a set of triggers / constraints that will validate the correctness of the data in the created B_Component .
    this method generates a couple of requests to the base for validation.

Is there a more canonical / elegant way for a similar structure?

Marked as a duplicate by D-side , vp_arth , Community Spirit ♦ 21 Aug '17 at 8:46 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • one
    Is that in A_C and B instead of a surrogate key to use some kind of real, composite, which includes a_id and refer to them, respectively, as a set of fields. but the solution is so-so - Mike
  • And sometimes the solution is thrown up by the real world under which the scheme is built, but according to this abstraction there are no such solutions - Mike
  • suppose it is A (pizza), A_C (toppings applicable to this pizza), B (pizza in order), B_C (pizza toppings in order). In this case, you generally have an erroneous data scheme that combines the template for creating an instance of an entity and the created instance — the connections AB and A_C-B_C do not exist, they should be deleted. - Akina
  • one
    @Akina apparently the vehicle wants to check at the database level, that only ingredients from the template can be added to the order - Mike
  • one
    @vp_arth I wrote about composite primary keys with the first comment. for some reason you didn’t react, I thought I didn’t like it, because there are also minuses in it, it is not always possible to find an adequate key - Mike

1 answer 1

Obviously, this structure is locked into a rhombus, where the database does not ensure the integrity of the data - nothing forces the B.a_id and A_Component.a_id fields to match.

This is an erroneous statement. For it to be correct, it is necessary that the correspondence of the values ​​B.a_id and A_Component.a_id be ensured (that is, these tables must be interconnected).

In the absence of such a correspondence, you have two independent chains of a linear connection, and in the diagram there should be two copies of table A - one of them is connected with B, the other with A_Component.

  • OK. Probably terminologically, I'm somewhere wrong. But how does your answer solve a problem? “so that the values ​​are ensured” is the question. How? - vp_arth