Search code examples
database-designrelational-databaseentity-relationship

Relationship between the compositions of two entities which have N2N relationship between themselves

I have two entities defined:

Product:
    ProductId  (PK)
    Name       (not null)

Parts:
    PartId     (PK)
    Name       (not null)

These have a many-to-many relationship defined between them. In my requirements I need to model another one-to-many relationship between the composite product/part item to some other composite product/part. In certain instances I also need to be able to link product without a part to product/part item. I have modeled it this way:

ProductPartLink:
    ProductPartLinkId  (PK)
    ProductId          (not null)
    PartId             (null)
    ParentId           (null)

For some reason this does not seem like its the best way to model this design. Without going into too much detail I cannot link to the many-to-many cross reference table because it might not exist yet (There are other relationships defined between the product and the parts).

Can anyone suggest another more efficient method?

UPDATE: This ProductPartLink is just a statistical/research purpose relationship and does not in way modify the relationship that is between a product and part. The reason I am having doubts over the way I modeled this is because I am implemented a hierarchy (using ProductPartLink .ParentId) between items that are not actually a hierarchy and will only have two levels.

Solution

  • It is not bidirectional. It is only the left side that can be either a product or a product/part combination.

    Assuming this means the right ("N") side must be product/part combination (it cannot be just product), it looks like you need something similar to this:

    enter image description here

    CHECK (NOT (ParentProductId IS NULL AND ParentPartId IS NOT NULL))

    FK1 and FK2 are standard junction table foreign keys. The FK3 ponts from ProductPart to Product. The FK4 self-references ProductPart.

    • If both ParentProductId and ParentPartId are NULL, neither FK3 nor FK4 are enforced and the product/part combination has no parent.
    • If just ParentProductId is non-NULL, FK4 is not enforced1 but FK3 is. The product/part combination has a product as its parent.
    • If both ParentProductId and ParentPartId are non-NULL, both FK3 and FK4 are enforced. The FK4 ensures the parent is a product/part combination (and not just product). The FK3 is "useless" in this case, since we already know the product must exist, otherwise product/part combination wouldn't.

    1 Assuming your DBMS supports MATCH SIMPLE foreign keys (most do).