数据库关系循环

Question

数据库关系周期听起来像是糟糕的数据库设计。下面是我认为无法避免的情况：

• 一家公司有地点（城市）

• 一家公司有 >产品（巨无霸）

• 产品是/是地点不可用（沙特阿拉伯无培根汉堡）

当前的设计允许您在地点提供不属于该公司的产品 em>确实属于该公司。

公司
1 - 麦当劳
2 - 汉堡王

位置
1 - 纽约，1 号楼 - 麦当劳 (1)
2 - 阿姆斯特丹，2 号楼 - 汉堡王 (2)

产品
1 - 巨无霸 - 麦当劳 (1)

产品位置
1 - 巨无霸 (1) - 阿姆斯特丹，2 号楼 (2)

麦当劳卖巨无霸，汉堡王不卖，但他们的大楼似乎有:)
当我们向产品添加也依赖于位置的关系时，情况会变得更糟。

我可以做什么来防止循环？
如何保证数据库数据的完整性？

Answer 1

循环依赖不会自动成为“糟糕的数据库设计”。从概念建模的角度来看，如果这种依赖关系准确地表示了您尝试建模的内容，那么它就不是“错误的”。

不幸的是，SQL 的局限性常常使得执行循环约束变得困难或不可能。在 SQL 中，您通常必须通过以某种方式打破约束或通过在过程代码中而不是通过数据库约束来实现规则来进行妥协。

Answer 2

如果我们从 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 是依赖实体（取决于公司）：

Answer 3

您真正需要的 SQL“断言”。然而不幸的是，当前的 DBMS 不支持这些。断言将类似于：

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 此处 的背景下发表了有关此内容的博客。

Answer 4

我不同意 - 这个说法是不正确的：

当前的设计可以让您
提供不属于的产品
本公司

如果产品不属于某个公司，则它不会具有该公司的外键。一个公司可以有多个产品，但一个产品只能属于一个公司。这是一对多的关系。

至于产品-位置，这听起来像是多对多的关系：一个产品可以在多个位置提供，一个位置可以销售许多产品。您需要一个 Product_Location JOIN 表。

更新：

您添加的记录仅澄清了问题。地点不仅仅是一座建筑物；麦当劳和汉堡王可能位于同一栋大楼内，但它们不在该大楼的同一位置。除了街道地址之外，您的位置表还需要其他列。我的意见仍然有效。如果你设计得当，汉堡王将无法销售巨无霸。你还没有做好；因此你感到困惑。

Answer 5

部分问题在于麦当劳和汉堡王都销售名为“汉堡包”和“芝士汉堡”以及（我认为）“双层芝士汉堡”的产品。因此，您存储在 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 将因违反外键约束而失败。

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