2

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?

Thanks.

entity-relationship

2
  • Can you describe which columns are involved in the FK between real_object_param and designed_object_param?
    – McNets
    Dec 19, 2016 at 15:18
  • in table real_object_param:FOREIGN KEY (id_designed_object_param) REFERENCES designed_object_param (id)
    – Luca
    Dec 20, 2016 at 8:08

2 Answers 2

0

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: http://rextester.com/GKU8783

2
  • The PK are wrong on real_object, real_object_param -- the id_real_object is allowed to repeat in first one, but is assumed to be unique in the second one. so you can have PK (id_real_object, id_designed_object) (1,1) (1,2) (two rows) but in the real_object_param only one (id_real_object, id_real_object_param) (1,1) is allowed. Dec 20, 2016 at 12:45
  • @DamirSudarevic you're right, PK of real_object_param should be PRIMARY KEY (id_real_object, id_designed_object, id_real_object_param), Thanks.
    – McNets
    Dec 20, 2016 at 13:48
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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