1

I constructed the following toy example to illustrate my question.

The left side of the schema describes a an abstract product consisting of its parts.

The right side of the schema describes actual produced instances of these products/parts. The horizontal relations indicate from which "template" the instance was built. Note that it is possible that a part_instance is initially not yet assigned to a product_instance.

enter image description here

Here's the challenge:

Assigning a part_instance to a product_instance should only be possible if the part from which the part_instance was built is actually a part of the product from which product_instance was built from. So the (optional) hierarchy on the right should be enforced to correspond to the hierarchy on the left.

Or as an example: Let's assume part1 belongs to product1 and part2 belongs to product2. Now let's create productInstance1 linked to product1, productInstance2 linked to product2, partInstance1 linked to part1 and partInstance2 linked to part2. It should be allowed to assign partInstance1 to productInstance1, but not to productInstance2. Similarly it should be allowed to assign partInstance2 to productInstance2 but not to productInstance1.

Question:

Is it possible to enforce this rule in the database?

5
  • Can't you just add the instance_id to the product_instance and part_instance to bind them to the certain instance at the top of hierarcy?
    – Kondybas
    Commented Mar 20, 2020 at 0:02
  • Yes, it's possible. Look at these questions and answers: database-design diamond" scheme Commented Mar 20, 2020 at 0:38
  • Also check this excellent and detailed answer by 1750 in a similar problem: dba.stackexchange.com/questions/120246/… Commented Mar 20, 2020 at 0:45
  • Thank you both for your inputs. I think I figured it out based on that information and I posted it as an answer. Feel free to comment on my answer if you feel like that can be improved further. Commented Mar 20, 2020 at 16:13
  • Yes , nice one. Commented Mar 20, 2020 at 21:17

1 Answer 1

3

Based on the comments to my question it looks like the solution is to add product_id to the primary key of both the part and product_instance tables.

enter image description here

1
  • Don't forget to mark your answer as the solution. It will help others looking at the question Commented Mar 21, 2020 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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