Блог

Циклические зависимости не являются автоматически «плохим дизайном базы данных». С точки зрения концептуального моделирования, если такая зависимость точно представляет то, что вы пытаетесь смоделировать, то это не «неправильно».

К сожалению, ограничения SQL часто затрудняют или делают невозможным применение ограничений, которые являются циклическими. В SQL вам обычно приходится идти на компромисс, каким-либо образом нарушая ограничение или реализуя правило в процедурном коде, а не через ограничения базы данных.

Если мы начнем с Location , Company и Product как независимых сущностей — как я думаю, вы пытались:

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

И если Product является зависимым объектом (зависит от компании):

Что вам действительно нужно в качестве SQL «утверждения». Однако, к сожалению, ни одна текущая СУБД не поддерживает это. Утверждение было бы чем-то вроде:

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

При отсутствии таковых другой возможностью является настройка ключей таким образом, чтобы можно было проверять правило:

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

Это гарантирует, что каждый company_product_locations ссылается на продукт и местоположение, связанные с одной и той же компанией.

Еще одна возможность для сложных ограничений — использовать материализованные представления. Я здесь писал об этом в блоге в контексте Oracle.

Я не согласен — это утверждение неверно:

Текущий дизайн позволил бы вам предлагать продукт, который не принадлежит этой компании

Если продукт не принадлежит компании, то у него не будет внешнего ключа для этой компании. У компании может быть много продуктов, но продукт может принадлежать только одной компании. Это отношение «один ко многим».

Что касается местоположения продукта, это звучит как отношение «многие ко многим»: продукт может предлагаться во многих местах, а местоположение может продавать много продуктов. Вам нужна таблица объединения Product_Location.

Обновить:

Добавленные вами записи только проясняют проблему. Местоположение — это больше, чем здание; McDonalds и Burger King могут находиться в одном здании, но они не находятся в одном и том же месте в этом здании. Вашей таблице местоположений понадобятся дополнительные столбцы помимо адреса улицы. Мои комментарии остаются в силе. Burger King не сможет продавать Биг Мак, если вы правильно его оформите. У вас это еще не получилось; отсюда ваша путаница.

Частично проблема заключается в том, что и McDonald’s, и Burger King продают продукты под названием «гамбургер» и «чизбургер» и (я думаю) «двойной чизбургер». Таким образом, информация, которую вы храните в ProductLocation, является неполной.

 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
  

И Даффимо прав, когда говорит, что «Местоположение — это больше, чем здание».

Вот один из способов реализовать эти ограничения. Я убрал идентификационные номера, потому что они имеют тенденцию скрывать, что происходит на самом деле.

 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');
  

Обратите внимание на перекрывающиеся внешние ключи в product_location. Перекрывающиеся внешние ключи гарантируют, что компания, идентифицированная с местоположением, и компания, идентифицированная с продуктом, являются одной и той же компанией. Теперь следующая ВСТАВКА завершится ошибкой из-за нарушения ограничения внешнего ключа.

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