Search code examples
database-designcyclerelationship

Database relationship cycle

Database relationship cycles smell like bad database design. Below is a situation in which I think it can not be prevented:

  • a Company has Locations (City)

  • a Company has Products (Big Mac)

  • Products are/are not available on Locations (No Bacon Burger in Saudia Arabia)

The current design would allow you to offer a Product that doesn't belong to this Company on a Location that does belong to this Company.

Company
1 - McDonalds
2 - Burger King

Location
1 - New York, building 1 - McDonalds (1)
2 - Amsterdam, building 2 - Burger King (2)

Product
1 - Big Mac - McDonalds (1)

ProductLocation
1 - Big Mac (1) - Amsterdam, building 2 (2)

McDonalds sells Big Macs, Burger King doesn't, but it seems their building does :)
It becomes worse when we add relationships to Product that are also Location dependent.

What can I do to prevent the cycle?
How do I ensure database data integrity?

Solution

  • If we start with Location, Company and Product as independent entities -- as I think you tried to:

    enter image description here

    create table ProductAtLocation (
      CompanyID  integer
    , LocationID integer
    , ProductID  integer
);

alter table ProductAtLocation
    add constraint pk_ProdLoc  primary key (CompanyID, LocationID, ProductID)
  , add constraint fk1_ProdLoc foreign key (CompanyID, LocationID) references CompanyLocation (CompanyID, LocationID)
  , add constraint fk2_ProdLoc foreign key (CompanyID, ProductID)  references CompanyProduct  (CompanyID, ProductID)
;

    And if the Product is a dependent entity (depends on company):

    enter image description here