Database relationship cycle

Question

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?

Answer 1

Cyclic dependencies are not automatically "bad database design". From a conceptual modelling point of view if such a dependency accurately represents what you are trying to model then it isn't "wrong".

Unfortunately the limitations of SQL often make it hard or impossible to enforce constraints that are cyclical. In SQL you will usually have to compromise by breaking the constraint in some way or by implementing the rule in procedural code rather than through database constraints.

Answer 2

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

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):