如何在SQL中引用其他几个实体的一个实体进行建模？ [英] How do I model one entity that references one of several other entities in SQL?

问题描述

我在制造DB的一部分工作。该业务采用定制订单并构建项目。他们只构建几个（让我们说3-10）类型的对象，但每种类型的对象在被记录的规范中是不同的。我想要一个主制造表（mfgorders），其中列出了一些常见的字段，然后参考特定于实体的规格表。我不完全相信这是正确的做法。其实我根本就没有信心。我所有的其他建模都是直截了当的，但是这是一个bug。

这里是SQL：

  CREATE TABLE dbo.mfgorders（MfgOrderId int NOT NULL 
 IDENTITY（1，1），
 OrderId int NOT NULL，
 LineNbr tinyint NOT NULL，
 MfgTypeId tinyint NOT NULL，
 ItemDescription varchar（999），
 ManufacturingCost smallmoney，
 CONSTRAINT PK_mfgorders PRIMARY KEY（MfgOrderId））; 
 --OrderId + LineNbr是一个复合引用lineitem表上的行（未在此处描述）
 
 CREATE TABLE dbo.mfgorders_entity1（MfgOrderId int NOT NULL，
 EntitySize decimal（ 5，3），
宽度十进制（4，2），
厚度十进制（4，2），
 CONSTRAINT PK_mfgorders_entity1 PRIMARY KEY（MfgOrderId））; 
 
 CREATE TABLE dbo.mfgorders_entity2（MfgOrderId int NOT NULL，
高度十进制（5,2），
宽度十进制（5,2），
厚度十进制4，2），
 RotationSetting decimal（4，1），
 FinishedHeight decimal（5,2），
 FinishedWidth decimal（5，2），
 FinishedThickness decimal（4， 2），
 CONSTRAINT PK_mfgorders_entity2 PRIMARY KEY（MfgOrderId））; 
 
 CREATE TABLE mfg_types（MfgTypeId tinyint NOT NULL，
 ItemName varchar（32）NOT NULL，
 ItemDescription nvarchar（64）NULL，
 IsActive位NOT NULL 
 CONSTRAINT DF_mfg_types_IsActive DEFAULT 1，
 SortOrder int NULL，
 CONSTRAINT PK_mfg_types PRIMARY KEY（MfgTypeId））; 
 
 ALTER TABLE dbo.mfgorders_entity1 ADD CONSTRAINT FK_mfgorders_entity1_mfgorders FOREIGN KEY（MfgOrderId）参考dbo.mfgorders（MfgOrderId）ON UPDATE NO ACTION ON DELETE CASCADE; 
 
 ALTER TABLE dbo.mfgorders_entity2 ADD CONSTRAINT FK_mfgorders_entity2_mfgorders FOREIGN KEY（MfgOrderId）参考dbo.mfgorders（MfgOrderId）ON UPDATE NO ACTION ON DELETE CASCADE; 
 
 ALTER TABLE dbo.mfgorders ADD CONSTRAINT FK_mfgorders_mfg_types FOREIGN KEY（MfgTypeId）REFERENCES dbo.mfg_types（MfgTypeId）ON UPDATE NO ACTION ON DELETE CASCADE; 
  

以下是上述代码的ER图：

模型意味着一个entity1和entity2可以具有相同的MfgOrderId，我当然不希望这样。我想让MfgOrderId只引用其中一个实体。我认为在我看来，我希望利用mfg_types来指向正确的实体表，但是我觉得模型是关闭的，并且通过询问SO社区来增加一些额外的时间。

Regards，
John

解决方案

您的设计是表达还是尝试to，是一个超类型和子类型的关系。

一种表达方式是将每个 MfgTypeId 实体表：

  CREATE TABLE dbo.mfgorders_entity2（MfgOrderId int NOT NULL，
 MfgTypeId tinyint NOT NULL，
高度十进制（5，2），
宽度十进制（5，2），
厚度十进制（4，2），
 RotationSetting decimal（4，1），
 FinishedHeight十进制（5，2），
 FinishedWidth decimal（5,2），
 FinishedThickness decimal（4，2），
 CONSTRAINT PK_mfgorders_entity2 PRIMARY KEY（MfgOrderId，MfgTypeId），
 CONSTRAINT chkEntity2_MfgTypeID CHECK（MfgTypeId =实体2的类型ID））; 
  

我也可能会更改 MfgOrders 表包括MfgTypeId作为主键的一部分。

  CREATE TABLE dbo.mfgorders（MfgOrderId int NOT NULL IDENTITY（1 ，1），
 MfgTypeId tinyint NOT NULL，
 OrderId int NOT NULL，
 LineNbr tinyint NOT NULL，
 ItemDescription varchar（999），
 ManufacturingCost smallmoney，
 CONSTRAINT PK_mfgorders PRIMARY KEY（MfgOrderId，MfgTypeId））; 
  

如果您搜索Supertype&子类型数据库建模可以找到许多资源，包括有关SO和Stackexchange网络的问题。我在下面列出了几个链接，可能有助于您开始：

1. 数据库管理员上的超级类型/子类型




2. Supertypes&子类型




3. how-to-implement-refferential-integrity-in-subtypes

I am working on a portion of a manufacturing DB. The business takes in custom orders and builds items to spec. They only build several (let's say 3-10) types of objects, but each type of object is different in the specifications that get recorded. I want to have a master manufacturing table (mfgorders) that lists some common fields and then have it refer to a specifications table that is specific to the entity ordered. I'm not entirely confident this is the right approach. In fact, I'm not confident at all. All of my other modelling has been straight forward, but this one is bugging me.

Here's the SQL:

CREATE TABLE dbo.mfgorders (MfgOrderId int NOT NULL
                                       IDENTITY (1, 1) ,
                        OrderId int NOT NULL,
                        LineNbr tinyint NOT NULL,
                        MfgTypeId tinyint NOT NULL,
                        ItemDescription varchar (999) ,
                        ManufacturingCost smallmoney,
                        CONSTRAINT PK_mfgorders PRIMARY KEY (MfgOrderId)) ;
--OrderId + LineNbr are a composite referencing a row on a lineitem table (not depicted here)

CREATE TABLE dbo.mfgorders_entity1 (MfgOrderId int NOT NULL,
                                EntitySize decimal (5, 3) ,
                                Width decimal (4, 2) ,
                                Thickness decimal (4, 2) ,
                                CONSTRAINT PK_mfgorders_entity1 PRIMARY KEY (MfgOrderId)) ;

CREATE TABLE dbo.mfgorders_entity2 (MfgOrderId int NOT NULL,
                                Height decimal (5, 2) ,
                                Width decimal (5, 2) ,
                                Thickness decimal (4, 2) ,
                                RotationSetting decimal (4, 1) ,
                                FinishedHeight decimal (5, 2) ,
                                FinishedWidth decimal (5, 2) ,
                                FinishedThickness decimal (4, 2) ,
                                CONSTRAINT PK_mfgorders_entity2 PRIMARY KEY (MfgOrderId)) ;

CREATE TABLE mfg_types (MfgTypeId tinyint NOT NULL,
                    ItemName varchar (32) NOT NULL,
                    ItemDescription nvarchar (64) NULL,
                    IsActive bit NOT NULL
                                 CONSTRAINT DF_mfg_types_IsActive DEFAULT 1,
                    SortOrder int NULL,
                    CONSTRAINT PK_mfg_types PRIMARY KEY (MfgTypeId)) ;

ALTER TABLE dbo.mfgorders_entity1 ADD CONSTRAINT FK_mfgorders_entity1_mfgorders FOREIGN KEY (MfgOrderId) REFERENCES dbo.mfgorders (MfgOrderId) ON UPDATE NO ACTION ON DELETE CASCADE;

ALTER TABLE dbo.mfgorders_entity2 ADD CONSTRAINT FK_mfgorders_entity2_mfgorders FOREIGN KEY (MfgOrderId) REFERENCES dbo.mfgorders (MfgOrderId) ON UPDATE NO ACTION ON DELETE CASCADE;

ALTER TABLE dbo.mfgorders ADD CONSTRAINT FK_mfgorders_mfg_types FOREIGN KEY (MfgTypeId) REFERENCES dbo.mfg_types (MfgTypeId) ON UPDATE NO ACTION ON DELETE CASCADE;

Here is an ER-diagram for the above code:

The model implies that an entity1 and entity2 can have the same MfgOrderId and I don't want that of course. I want the MfgOrderId to refer to only one of the entities. I think in my mind I was hoping to utilize the mfg_types to point to the right entity table, but I feel the model is off and I'd add a few extra years to my life by asking the SO community.

Regards, John

解决方案

What your design is expressing, or attempting to, is a Supertype and Subtype relationship.

One way to express this is to include the MfgTypeId field in each Entity table:

CREATE TABLE dbo.mfgorders_entity2 (MfgOrderId int NOT NULL,
                 MfgTypeId tinyint NOT NULL,
                 Height decimal (5, 2) ,
                 Width decimal (5, 2) ,
                 Thickness decimal (4, 2) ,
                 RotationSetting decimal (4, 1) ,
                 FinishedHeight decimal (5, 2) ,
                 FinishedWidth decimal (5, 2) ,
                 FinishedThickness decimal (4, 2) ,
                 CONSTRAINT PK_mfgorders_entity2 PRIMARY KEY (MfgOrderId, MfgTypeId),
                 CONSTRAINT chkEntity2_MfgTypeID CHECK (MfgTypeId = 'Type Id for Entity 2')) ;

I would probably also alter the MfgOrders table to include the MfgTypeId as part of the primary key as well.

CREATE TABLE dbo.mfgorders (MfgOrderId int NOT NULL IDENTITY (1, 1) ,
                 MfgTypeId tinyint NOT NULL,                 
                 OrderId int NOT NULL,
                 LineNbr tinyint NOT NULL,
                 ItemDescription varchar (999) ,
                 ManufacturingCost smallmoney,
                 CONSTRAINT PK_mfgorders PRIMARY KEY (MfgOrderId, MfgTypeId)) ;

If you search for Supertype & Subtype database modeling you'll find a number of resources, including questions on SO and the Stackexchange network. I've included a few links below which might help you get started with this:

1. Supertype/Subtype on Database Administrators

2. Supertypes & Subtypes

3. how-to-implement-referential-integrity-in-subtypes

这篇关于如何在SQL中引用其他几个实体的一个实体进行建模？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！