How do I model one entity that references one of several other entities in SQL?

Question

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

Answer 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:

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