Two paths between two entities

Posted on

Question :

In my db the entities are:

  • designed_object: a generic designed object
  • designed_object_param: a parameter related to the designed object with the type (param_type), the default value (param_value), the maximum and minimum value (param_max/min_value)

  • real_object: an instance of the designed object

  • real_object_param: the actual parameters of the instance (param_value)

They are related as follow (foreign key):

  • designed_object – real_object: 1..n
  • designed_object – designed_object_param: 1..n
  • real_object – real_object_param: 1..n
  • designed_object_param – real_object_param: 1..1

The problem is that there are two paths from real_object_param to designed_object:

  • real_object_param -> real_object -> designed_object
  • real_object_param -> designed_object_param -> designed_object

With this design, it is possible that, starting from the same real_object_param, the two paths point to two different rows if, for some bug, the real_object_param refers a wrong designed_object_param.
I know that this can be avoided through a trigger.
Is there any better design?



Answer :

In your actual schema, you could add a designed_object_param assigned to a different designed_object.

If you want a 1 – 1 constraint, add a id_designed_object field to the real_object_param, and compose the FK as (id_designed_object, id_designed_object_param). This will ensure that only params of the designed object can be added to the real_object_param.

You can also add id_designed_object to the FK between real_object_param and real_object as (id_object, id_designed_object). I think this covers all possibilities.

The final schema should be similar to this:

create temp table designed_object
    id_designed_object int not null PRIMARY KEY

create temp table designed_object_param
    id_designed_object int4 not null REFERENCES designed_object (id_designed_object),
    id_designed_object_param int not null,
    PRIMARY KEY (id_designed_object, id_designed_object_param)

create temp table real_object
    id_real_object int4 not null,
    id_designed_object int4 not null REFERENCES designed_object (id_designed_object),
    PRIMARY KEY (id_real_object, id_designed_object)

create temp table real_object_param
    id_real_object int4 not null,
    id_real_object_param int4 not null,
    id_designed_object int4 not null,
    id_designed_object_param int4 not null,
    PRIMARY KEY (id_real_object, id_designed_object, id_real_object_param),
    FOREIGN KEY (id_real_object, id_designed_object) REFERENCES real_object (id_real_object, id_designed_object),
    FOREIGN KEY (id_designed_object, id_designed_object_param) REFERENCES designed_object_param (id_designed_object, id_designed_object_param)

Can check it here:

I renamed few things here, just to make it easier for me to think. Most of it is self-explanatory. In essence, the problem and the solution is very similar to the previous question, with more detailed answer.

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *