sql-server – 在SQL Server中设计条件数据库关系

我有三种基本类型的实体：人员,企业和资产.每个资产可以由一个且仅一个人或企业拥有.每个人员和企业可以拥有0到多个资产.在Microsoft SQL Server中存储此类条件关系的最佳做法是什么？

我最初的计划是在Assets表中有两个可以为空的外键,一个用于People,一个用于Business.其中一个值将为null,而另一个将指向所有者.我在这种设置中看到的问题是它需要应用程序逻辑才能被解释和执行.这真的是最好的解决方案还是有其他选择？

解决方法

我建议你使用超类型和子类型.首先,创建PartyType和Party表：

CREATE TABLE dbo.PartyType (
   PartyTypeID int NOT NULL identity(1,1) CONSTRAINT PK_PartyType PRIMARY KEY CLUSTERED
   Name varchar(32) CONSTRAINT UQ_PartyType_Name UNIQUE
);

INSERT dbo.PartyType VALUES ('Person'),('Business');

超

CREATE TABLE dbo.Party (
   PartyID int identity(1,1) NOT NULL CONSTRAINT PK_Party PRIMARY KEY CLUSTERED,FullName varchar(64) NOT NULL,BeginDate smalldatetime,-- DOB for people or creation date for business
   PartyTypeID int NOT NULL
      CONSTRAINT FK_Party_PartyTypeID FOREIGN KEY REFERENCES dbo.PartyType (PartyTypeID)
);

亚型

然后,如果存在Person的唯一列,则创建一个Person表,其中只包含：

CREATE TABLE dbo.Person (
   PersonPartyID int NOT NULL
      CONSTRAINT PK_Person PRIMARY KEY CLUSTERED
      CONSTRAINT FK_Person_PersonPartyID FOREIGN KEY REFERENCES dbo.Party (PartyID)
         ON DELETE CASCADE,-- add columns unique to people
);

如果存在对于企业而言唯一的列,请创建一个仅包含以下内容的Business表：

CREATE TABLE dbo.Business (
   BusinessPartyID int NOT NULL
      CONSTRAINT PK_Business PRIMARY KEY CLUSTERED
      CONSTRAINT FK_Business_BusinessPartyID FOREIGN KEY REFERENCES dbo.Party (PartyID)
         ON DELETE CASCADE,-- add columns unique to businesses
);

用法和注意事项

最后,您的Asset表将如下所示：

CREATE TABLE dbo.Asset (
   AssetID int NOT NULL identity(1,1) CONSTRAINT PK_Asset PRIMARY KEY CLUSTERED,PartyID int NOT NULL
      CONSTRAINT FK_Asset_PartyID FOREIGN KEY REFERENCES dbo.Party (PartyID),AssetTag varchar(64) CONSTRAINT UQ_Asset_AssetTag UNIQUE
);

超类型Party表与子类型表Business和Person共享的关系是“一对零或一”.现在,虽然子类型通常在另一个表中没有相应的行,但在这种设计中有可能让一个Party最终在两个表中.但是,您可能真的喜欢这样：有时一个人和一个企业几乎可以互换.如果没有用,虽然执行此操作的触发器相当容易,但最佳解决方案可能是将PartyTypeID列添加到子类型表中,使其成为PK&的一部分. FK,并在PartyTypeID上放置一个CHECK约束.

此模型的优点在于,当您要创建对业务或人员具有约束的列时,则将约束设置为适当的表而不是聚会表.

此外,如果需要,在约束上启用级联删除可能很有用,以及子类型表上的INSTEAD OF DELETE触发器,而是从超类型表中删除相应的ID(这可以保证不存在没有子类型行的超类型行).这些查询非常简单,可以在整行存在或不存在的级别上工作,在我看来,这是对需要检查列值一致性的任何设计的巨大改进.

此外,请注意,在许多情况下,您认为应该在其中一个子类型表中的列实际上可以组合在超类型表中,例如社会安全号.称之为TIN(纳税人识别号码),它适用于企业和人.

ID列命名

是否在Person表PartyID,PersonID或PersonPartyID中调用列的问题是您自己的首选项,但我认为最好将这些PersonPartyID或BusinessPartyID称为容忍较长名称的成本,这样可以避免两种类型的混乱.例如,不熟悉数据库的人看到了BusinessID,并且不知道这是PartyID,或者看到PartyID,并且不知道它被外键限制在Business表中.

如果你想为Party和Business表创建视图,它们甚至可以是物化视图,因为它是一个简单的内连接,如果你真的如此倾向,你可以将PersonPartyID列重命名为PersonID(尽管我不愿意).如果它对你有很大的价值,你甚至可以在这些视图上创建INSTEAD OF INSERT和INSTEAD OF UPDATE触发器来处理两个表的插入,使得视图看起来像他们自己的表一样对很多应用程序.

使您的建议设计按原样运行

另外,我不想提及它,但是如果你想在你提出的设计中有一个约束来强制只填充一个列,这里是代码：

ALTER TABLE dbo.Assets
ADD CONSTRAINT CK_Asset_PersonOrBusiness CHECK (
   CASE WHEN PersonID IS NULL THEN 0 ELSE 1 END
   + CASE WHEN BusinessID IS NULL THEN 0 ELSE 1 END = 1
);

但是,我不推荐这种解决方案.

最后的想法

一个自然的第三个子类型是组织,从人和企业可以拥有成员资格的意义上来说.超类型和子类型也优雅地解决客户/员工,客户/供应商以及与您提出的类似的其他问题.

小心不要混淆“Is-A”和“Acts-As-A”.您可以通过查看订单表或查看订单计数来告知聚会是客户,并且可能根本不需要客户表.同样不要将身份与生命周期混淆：租车可能最终会被出售,但这是生命周期的进展,应该用列数据处理,而不是表存在 – 汽车不是以RentalCar和后来变成了ForSaleCar,它一直都是Car.或者也许是RentalItem,也许企业也会租用其他东西.你明白了.

甚至可能不需要PartyType表.可以通过相应子类型表中存在行来确定聚会类型.这还可以避免PartyTypeID与子类型表存在不匹配的潜在问题.一种可能的实现是保留PartyType表,但从Party表中删除PartyTypeID,然后在Party表的视图中根据哪个子类型表具有相应的行返回正确的PartyTypeID.如果您选择允许派对成为两种子类型,则无效.然后,您只需坚持使用子类型视图,并知道BusinessID和PersonID的相同值指向同一方.

进一步阅读这种模式

请参阅A Universal Person and Organization Data Model以获得更完整和理论上的处理.

我最近发现以下文章对于描述在数据库中建模继承的一些替代方法很有用.虽然特定于Microsoft的Entity Framework ORM工具,但您无法在任何数据库开发中自行实现这些工具：

（编辑：云计算网_泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!