1

We have a database structure like the one shown in the (very simplified) diagram below:

enter image description here

As you can see, there is a table Categories, holding data like this:

Id Description
1  Car
2  Truck

The table VehicleParts holds the data that correlates which vehicle has which part.

My question

How can I guarantee that one row in the VehicleParts table does not reference a Version of a Car and a Part of a Truck or vice versa?

MDCCL
  • 8,520
  • 3
  • 30
  • 61
Guilherme
  • 111
  • 4
  • 1
    I'm not clear on the relationship here. Can Cars not have Versions, and can Trucks not have Parts? – Randolph West Jul 07 '17 at 04:19
  • @RandolphWest Yes. Car and Truck are just example data of the table Categories. As they are only 1 to N relationship, they can have 0 Models, 0 Versions, 0 Sets and 0 Parts; i.e. if the user add a new row on the categories table, "Motorcycles". – Guilherme Jul 07 '17 at 06:11
  • You might have to get creative with triggers and / or default constraints. – Randolph West Jul 07 '17 at 06:15
  • Yes, you add CategoryID to Versions, Parts and VehicleParts and modify Forein Key and Unique constraints appropriately. – ypercubeᵀᴹ Jul 07 '17 at 06:26
  • @ypercubeᵀᴹ Thanks for your comment, and the related question. So, adding CategoryID to Versions and Parts does not introduce some data redundancy? i.e. We can not assume that a Version that is related to a Model that is related to a Car is a Car? – Guilherme Jul 07 '17 at 06:34
  • Yes, you also modify the FKs to be composite (include the CategoryID). There is redundnancy but you can't avoid it, if you want to enforce this kind of constraint. The other way would be to use triggers or restrict access to stored procedures (for INSERT/DELETE/UPDATE in the related tables) that take care / enforce it. – ypercubeᵀᴹ Jul 07 '17 at 06:37
  • @ypercubeᵀᴹ Ah, I see. The comments on the linked question are about that as well... (btw awesome answer on the other question. It is very close to mine, and unfortunately I do not find it before posting). – Guilherme Jul 07 '17 at 06:43
  • 1
    See my answer there. I have also links to 4 or 5 similar questions. Your schema is a tiny bit different, it has 2 tables (Sets, Parts) in one side/path of the "diamond" shape. But it doesn't change the proposed solution. – ypercubeᵀᴹ Jul 07 '17 at 06:44
  • @ypercubeᵀᴹ Understood. The name of this kind of relationships is technically called "diamond shape"? – Guilherme Jul 07 '17 at 06:53
  • No, that's mine because the design looks like that of drawn on paper. I don't know an official/technical term. – ypercubeᵀᴹ Jul 07 '17 at 06:55

0 Answers0