如何在没有复合键的情况下执行二级关系？ [英] How can I enforce second-degree relationships without composite keys?

问题描述

考虑这个数据库设计用于多租户业务Web应用程序：

A 租户是网络应用程序的租户，一个租户有许多商店和许多客户（客户记录不在租户之间共享，因此它对多个客户记录指的是相同的现实生活中的人物），每个 Shop 有许多作业。 Job 也与每个客户相关联。

存在一个问题，因为似乎没有一个简单的约束解决方案来防止 Job 的 CustomerId 更改为不属于父级租户的客户，从而创建无效数据。 p>

以下是现有架构：

  CREATE TABLE租户（
 TenantId bigint IDENTITY（1,1）PRIMARY KEY 
 ... 
）
 
 CREATE TABLE商店（
 TenantId bigint FOREIGN KEY（Tenants.TenantId），
 ShopId bigint IDENTITY（1,1）PRIMAREY KEY，
 ... 
）
 
 CREATE TABLE客户（
 TenantId bigint FOREIGN KEY（Tenants.TenantId ），
 CustomerId bigint IDENTITY（1,1）PRIMARY KEY 
 ... 
）
 
 CREATE TABLE作业（
 ShopId bigint FOREIGN KEY .ShopId）
 JobId bigint IDENTITY（1,1）PRIMARY KEY，
 CustomerId bigint FOREIGN KEY（Customers.CustomerId）
）
  

目前，我唯一可以想到的解决方案是将设计更改为使用总是包含父 Tenant.TenantId ，然后相应地分享：

  CREATE TABLE商店（
 TenantId bigint，
 ShopId bigint IDENTITY（1,1），
 ... 
 
 PRIMARY KEY（TenantId，ShopId）
 FOREIGN KEY（TenantId REFERENCES Tenants（TenantId））
）
 
 CREATE TABLE客户（
 TenantId bigint，
 CustomerId bigint IDENTITY（1,1）
 ... 
 
 PRIMARY KEY（TenantId， CustomerId）
 FOREIGN KEY（TenantId参考租户（TenantId））
）
 
 CREATE TABLE作业（
 TenantId bigint 
 ShopId bigint 
 JobId bigint IDENTITY（1,1），
 CustomerId bigint 
 
 PRIMARY KEY（TenantId，ShopId，JobId）
 
 FOREIGN KEY（TenantId REFERENCES Tenants（TenantId））
 FOREIGN KEY（TenantId，ShopId参考商店（TenantId，ShopID））
 FOREIGN KEY（TenantId，CustomerId参考客户（TenantId，CustomerId））
）
  

...似乎有点黑客，但有很多冗余数据 - 特别是 IDENTITY 也被使用。复制外键约束是完全有效的，有益的是，RDBMS可以在数据被突变时测试JOIN的一致性吗？

解决方案

，但是您不需要复合主键来使用它们！您只需要引用表中的复合索引。 Jobs 中的冗余 TenantId 由于FK约束，不会产生更新异常的风险。

例如：

  CREATE TABLE商店（
 ShopId bigint IDENTITY 1,1），
 TenantId bigint，
 PRIMARY KEY（ShopId），
 UNIQUE KEY（TenantId，ShopId），
 FOREIGN KEY（TenantId）参考租户（TenantId）
 
 
 CREATE TABLE客户（
 CustomerId bigint IDENTITY（1,1），
 TenantId bigint，
 PRIMARY KEY（CustomerId），
 UNIQUE KEY（TenantId，CustomerId），
 FOREIGN KEY（TenantId）参考租户（TenantId）
）
 
 CREATE TABLE作业（
 JobId bigint IDENTITY（1,1） ，
 TenantId bigint，
 ShopId bigint，
 CustomerId bigint，
 PRIMARY KEY（JobId），
 FOREIGN KEY（TenantId，ShopId）参考商店（TenantId，ShopID） ，
 FOREIGN KEY（TenantId，CustomerId）参考客户（TenantId，CustomerId）
）
  

如果您担心存储空间，建议您根据实际的数据量计算该空间的实际成本，并对FK约束与触发器之间的性能差异进行基准测试，并对涉及子查询的检查约束进行基准测试。不要仅仅假设一个额外的属性将是无效的。

Consider this database design for a multi-tenancy line-of-business web application:

A Tenant is a tenant of the web-application, a Tenant has many Shops and many Customers (Customer records are not shared between Tenants, so it's valid for multiple Customer records to refer to the same real-life human), and each Shop has many Jobs. A Job is also associated with each Customer.

There exists a problem in that there doesn't seem to be a trivial constraint solution to prevent the case where a Job's CustomerId is changed to a Customer that does not belong to the parent Tenant, thus creating invalid data.

Here is the present schema:

CREATE TABLE Tenants (
    TenantId bigint IDENTITY(1,1) PRIMARY KEY
    ...
)

CREATE TABLE Shops (
    TenantId bigint FOREIGN KEY( Tenants.TenantId ),
    ShopId   bigint IDENTITY(1,1) PRIMAREY KEY,
    ...
)

CREATE TABLE Customers (
    TenantId   bigint FOREIGN KEY( Tenants.TenantId ),
    CustomerId bigint IDENTITY(1,1) PRIMARY KEY
    ...
)

CREATE TABLE Jobs (
    ShopId bigint FOREIGN KEY( Shops.ShopId )
    JobId bigint IDENTITY(1,1) PRIMARY KEY,
    CustomerId bigint FOREIGN KEY( Customers.CustomerId )
)

Currently the only solution I can think of is to change the design to use Composite Keys that always include the parent Tenant.TenantId, which are then shared accordingly:

CREATE TABLE Shops (
    TenantId bigint,
    ShopId   bigint IDENTITY(1,1),
    ...

    PRIMARY KEY( TenantId, ShopId )
    FOREIGN KEY( TenantId REFERENCES Tenants (TenantId) )
)

CREATE TABLE Customers (
    TenantId   bigint,
    CustomerId bigint IDENTITY(1,1)
    ...

    PRIMARY KEY( TenantId, CustomerId )
    FOREIGN KEY( TenantId REFERENCES Tenants (TenantId) )
)

CREATE TABLE Jobs (
    TenantId bigint
    ShopId bigint
    JobId bigint IDENTITY(1,1),
    CustomerId bigint

    PRIMARY KEY( TenantId, ShopId, JobId )

    FOREIGN KEY( TenantId REFERENCES Tenants ( TenantId ) )
    FOREIGN KEY( TenantId, ShopId REFERENCES Shops( TenantId, ShopID ) )
    FOREIGN KEY( TenantId, CustomerId REFERENCES Customers( TenantId, CustomerId ) )
)

...seems like a bit of a hack though, with lots of redundant data - especially as IDENTITY is used anyway. Is there any way the RDBMS can test JOINs for consistency whenever data is mutated?

解决方案

Composite foreign key constraints are perfectly valid and useful, but you don't need composite primary keys to use them! You just need composite indices in the referenced tables. The redundant TenantId in Jobs won't create a risk of update anomalies thanks to the FK constraints.

For example:

CREATE TABLE Shops (
    ShopId   bigint IDENTITY(1,1),
    TenantId bigint,
    PRIMARY KEY (ShopId),
    UNIQUE KEY (TenantId, ShopId),
    FOREIGN KEY (TenantId) REFERENCES Tenants (TenantId)
)

CREATE TABLE Customers (
    CustomerId bigint IDENTITY(1,1),
    TenantId   bigint,
    PRIMARY KEY (CustomerId),
    UNIQUE KEY (TenantId, CustomerId),
    FOREIGN KEY (TenantId) REFERENCES Tenants (TenantId)
)

CREATE TABLE Jobs (
    JobId      bigint IDENTITY(1,1),
    TenantId   bigint,
    ShopId     bigint,
    CustomerId bigint,
    PRIMARY KEY (JobId),
    FOREIGN KEY (TenantId, ShopId) REFERENCES Shops (TenantId, ShopID),
    FOREIGN KEY (TenantId, CustomerId) REFERENCES Customers (TenantId, CustomerId)
)

If you're concerned about storage space, I suggest you calculate the actual cost of that space based on realistic volumes of data and benchmark the performance differences between FK constraints vs triggers vs check constraints involving a subquery. Don't just assume an extra attribute will be inefficient.

这篇关于如何在没有复合键的情况下执行二级关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！