1

The following use case has been bugging me from time to time and it always annoyed me that I don't really know a solution for this. Recently I came across it once more:

  • An object has 0 or more objectProperties;
  • an object is used in 1 or more operations;
  • some of the operations use 1 or more objectProperties of the object they are operating on (0 or more relation);
  • an objectProperty will be used in 1 or more operations.

This leads me to the following database design:
circular referential integrity problem erd
Note: the table names are made up to illustrate the problem


My questions are:

  • Is this db design correct for the stated use case?
  • Is there any method to force referential integrity for the use case as described within the database engine*?

*) I can enforce this rule in my application code, but that strikes me as rather fragile, so I would rather see the integrity enforced by constraints in my data model, if at all possible.

Monika
  • 265
  • 2
  • 8
  • 1
    NEVER under any circumstances enforce data or referential integrity through anything except the database,. That will result in bad data 100% of the time. Applications are not the only thing that can change database data. – HLGEM Mar 14 '16 at 20:23
  • Also do not design databases using object oriented principles. If you have a circular reference of any kind the database design is incorrect. Without more information than is given, IT is hard to say what the actual design should be. – HLGEM Mar 14 '16 at 20:25
  • @HLGEM, thanks for the support, the big question remains: how to enforce the integrity in the use case as presented in the OP? – Monika Mar 14 '16 at 20:25
  • I would likely remove object Id from operation and use a junction table to connect it to the Object property which in turns connects to the object, so Object Id is not needed in the operations table. But without seeing more information about the meaning of what is happening (modelling buy generic names is almost alawys a poor idea), Ican;t tell for sure what you really need to do. – HLGEM Mar 14 '16 at 20:30
  • @HLGEM, I can't see your proposed solution work because *only* some of the `operations` use `objectProperties`, while others do not use any `objectProperties` at all. Am I missing some sql mysteries here? – Monika Mar 14 '16 at 20:38

1 Answers1

1

Your diagram correctly represents the stated requirements.

To enforce referential integrity, you can add objectId to the operationUsingObjectProperty table. Then, you can create composite foreign key constraints on objectId, objectPropertyId and objectId, operationId.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • In a somewhat more complex schema, the `objectId` would have to be added to a series of tables to complete the circular integrity I guess... Would adding `objectId` to the `operationUsingObjectProperty` be considered a denormalisation? – Monika Mar 15 '16 at 22:25
  • Yes, by including a transitive dependency we're denormalizing to 2NF. However, the FK constraints will prevent update anomalies. A more expensive check constraint could also be used to join `objectProperty` with `operation` to ensure integrity without denormalization. – reaanb Mar 16 '16 at 06:20