如何建模一个引用SQL中其他几个实体之一的实体？

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.

我正在研究制造数据库的一部分。该业务接收自定义订单并根据规范构建项目。它们只构建了几种(比方说3-10种)对象类型,但每种类型的对象在记录的规范中都是不同的。我想要一个主制造表(mfgorders)列出一些常见字段,然后让它引用特定于所订购实体的规格表。我并不完全相信这是正确的做法。事实上,我根本没有信心。我所有的其他建模都是直截了当的,但是这个让我烦恼。

Here's the SQL:

这是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:

以下是上述代码的ER图:

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.

该模型暗示实体1和实体2可以具有相同的MfgOrderId,当然我不希望这样。我希望MfgOrderId只引用其中一个实体。我想在我的脑海里,我希望利用mfg_types指向正确的实体表,但我觉得这个模型已经关闭了,我想通过询问SO社区来增加一些生命。

Regards, John

3 个解决方案

#1

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:

表达此方法的一种方法是在每个Entity表中包含MfgTypeId字段:

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.

我可能还会改变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)) ;

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:

如果您搜索超类型和子类型数据库建模,您将找到许多资源,包括有关SO和Stackexchange网络的问题。我在下面添加了一些链接,可能有助于您开始使用此功能:

1. Supertype/Subtype on Database Administrators

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

2. Supertypes & Subtypes

   超类型和子类型

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

#2

"...and I don't want that of course."

“......我当然不想那样做。”

I wouldn't necessarily jump to that conclusion. The model you're describing is an acceptable one, Hibernate calls it InheritanceType.JOINED. You can read about it in their docs, but you would model that relationship with all the tables sharing the same ID.

我不一定会跳到那个结论。您描述的模型是可接受的模型,Hibernate将其称为InheritanceType.JOINED。您可以在他们的文档中阅读它,但您可以使用共享相同ID的所有表来建模该关系。

The JPA docs also talk about it.

JPA文档也谈到了它。

The key part about using this, or indeed about representing a class hierarchy in the database in general, is that you need to be able to tell whether you have an entity1 or an entity2. The most well-defined way of doing this is with a Discriminator, basically a column in the superclass table that says whether any row represents an entity1 or an entity2.

关于使用它,或者实际上关于在数据库中表示类层次结构的关键部分是,您需要能够判断您是否具有entity1或entity2。最明确定义的方法是使用Discriminator,它基本上是超类表中的一列,表示任何行是代表entity1还是entity2。

I believe that if you don't specify a Discriminator, and you're using JOINED, then Hibernate (and perhaps other JPA implementations) will do it for you automatically by checking all subclass tables and seeing which one contains a row with a particular ID. Assuming every MfgOrderId is in one and only one subclass table, this would work for you without changing your schema.

我相信如果您没有指定Discriminator,并且您正在使用JOINED,那么Hibernate(以及可能还有其他JPA实现)将通过检查所有子类表并查看哪一个包含具有特定ID的行来自动执行此操作。假设每个MfgOrderId都在一个且只有一个子类表中,这对您无需更改架构即可。

EDIT

I mistakenly switched TABLE_PER_CLASS, I meant JOINED!

我错误地切换了TABLE_PER_CLASS,我的意思是加入!

#3

I can't see any major problems with what you've got. Here is what I would probably do:

我看不出你有什么重大问题。这是我可能会做的:

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( MfgOrderEntity1Id int NOT NULL IDENTITY (1, 1),
                                MfgOrderId int NOT NULL,
                                EntitySize decimal (5, 3) ,
                                Width decimal (4, 2) ,
                                Thickness decimal (4, 2) ,
                                CONSTRAINT PK_mfgorders_entity1 PRIMARY KEY (MfgOrderEntity1Id)) ;

CREATE TABLE dbo.mfgorders_entity2 (MfgOrderEntity2Id int NOT NULL IDENTITY (1, 1),
                                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 (MfgOrderEntity2Id)) ;

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;