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 h开发者_开发技巧as 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?

---

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.

---

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

---

What you really need as a SQL "assertion". However unfortunately no current DBMS supports these. The assertion would be something like:

assertion product_location_check
check (not exists (select null
                   from   company_product_location cpl
                   where  not exists
                   ( select null
                     from   company_products cp
                     join   company_locations cl on c1.company_id = cp.company_id
                     and    cp.product_id = cpl.product_id
                     and    cl.location_id = cpl.location_id
                     and    cp.company_id = cpl.company_id
                   )
                  )
      );

In the absence of these, another possibility is set up the keys such that the rule can be checked:

create table company_products
( company_id references companies
, product_id ...
, primary key (company_id, product_id)
);

create table company_locations
( company_id references companies
, location_id ...
, primary key (company_id, location_id)
);

create table company_product_locations
( company_id ...
, product_id ...
, location_id ...
, primary key (company_id, product_id, location_id)
, foreign key (company_id, product_id) references company_products)
, foreign key (company_id, location_id) references company_locations)
);

This ensures that each company_product_locations references a product and a location associated with the same company.

Yet another possibility for complex constraints is to use materialized views. I have blogged about this in the context of Oracle here.

---

I disagree - this statement is incorrect:

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

If a Product does not belong to a Company, then it won't have a foreign key to that Company. A Company may have many Products, but a Product can only belong to one company. That's a one-to-many relationship.

As for Product-Location, that sounds like a many-to-many relationship: a Product can be offered at many Locations, and a Location can sell many Products. You need a Product_Location JOIN table.

UPDATE:

The records you added only clarify the issue. A location is more than a building; McDonalds and Burger King might be in the same building, but they aren't in the same location in that building. Your Location table will need additional columns besides the street address. My comments still stand. Burger King will not be able to sell a Big Mac if you design this properly. You don't have it right yet; hence your confusion.

---

Part of the problem is that both McDonald's and Burger King sell products called "hamburger" and "cheeseburger" and (I think) "double cheeseburger". So the information you're storing in ProductLocation is incomplete.

Product
--
Big Mac    McDonald's
Hamburger  McDonald's
Hamburger  Burger King

ProductLocation
Big Mac    McDonald's   New York, building 1
Hamburger  McDonald's   New York, building 1
Hamburger  Burger King  Amsterdam, building 2

And duffymo is right when he says "A location is more than a building."

Here's one way to implement these constraints. I dropped the id numbers, because they tend to hide what's really happening.

create table company (
  co_name varchar(15) primary key
);

insert into company values 
('McDonald''s'),
('Burger King');

create table location (
  loc_name varchar(30) primary key,
  co_name varchar(15) not null references company (co_name),
  unique (loc_name, co_name)
);

insert into location values 
('New York, building 1', 'McDonald''s'),
('Amsterdam, building 2', 'Burger King');

create table product (
  co_name varchar(15) not null references company (co_name),
  product_name varchar(15) not null,
  primary key (co_name, product_name)
);

insert into product values
('McDonald''s', 'Big Mac'),
('McDonald''s', 'Hamburger'),
('McDonald''s', 'Cheeseburger'),
('Burger King', 'Hamburger'),
('Burger King', 'Cheeseburger');

create table product_location (
  loc_name varchar(30) not null references location (loc_name),
  co_name varchar(15) not null,
  product_name varchar(15) not null,
  foreign key (co_name, product_name) references product (co_name, product_name),
  foreign key (loc_name, co_name) references location (loc_name, co_name),
  primary key (loc_name, co_name, product_name)
);

insert into product_location values 
('Amsterdam, building 2', 'Burger King', 'Cheeseburger');

Note the overlapping foreign keys in product_location. Overlapping foreign keys guarantee that the company identified with the location and the company identified with the product are the same company. Now the following INSERT will fail with a foreign key constraint violation.

insert into product_location values 
('Amsterdam, building 2', 'McDonald''s', 'Cheeseburger');