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?
In your actual schema, you could add a
designed_object_param assigned to a different
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
You can also add
id_designed_object to the FK between
(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
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.