Sql-server – Is the implementation of type/subtype design pattern (for mutually exclusive subclasses) correct

database-designsql serversql-server-2012subtypes

Introduction

In order for this question to be useful for future readers I will use the generic data model to illustrate the problems I face.

Our data model consists of two entities, which shall be labeled as A and B. In order to keep things simple, all their attributes will be of int type.

Entity A has following attributes: D and X;
Entity B has following attributes: D and Y;

Problem

Since both entities share common attribute D, I have decided to apply type/subtype design.

I am not sure that my implementation is correct, thus I am asking here for design review.

My Implementation

-- lookup table for discriminator column
CREATE TABLE ClassType
(
  ClassTypeID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Class_Description VARCHAR(50) NOT NULL
);

-- inserting types A and B from our example 
INSERT INTO ClassType (Class_Description)
VALUES ('A'), ('B');

-- creating base class table
CREATE TABLE BaseClass
(
  BaseClass_ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ClassTypeID INT NOT NULL, -- FK to Type
  D int
);

ALTER TABLE BaseClass
ADD CONSTRAINT [FK_BaseClass_ClassType]
FOREIGN KEY (ClassTypeID)
REFERENCES ClassType (ClassTypeID);

-- we need this constraint in order for foreign keys in subclasses to work
ALTER TABLE BaseClass
ADD CONSTRAINT [FK_AltKey]
UNIQUE (BaseClass_ID, ClassTypeID);

-- creating subclasses:
CREATE TABLE SubclassA
(
  BaseClass_ID INT NOT NULL PRIMARY KEY,
  X int,
  ClassTypeID AS 1 PERSISTED -- calculated field, ensures integrity
);

ALTER TABLE SubclassA
ADD CONSTRAINT [FK_SubclassA_BaseClass]
FOREIGN KEY (BaseClass_ID, ClassTypeID)
REFERENCES BaseClass (BaseClass_ID, ClassTypeID);

CREATE TABLE SubclassB
(
  BaseClass_ID INT NOT NULL PRIMARY KEY,
  Y int,
  ClassTypeID AS 2 PERSISTED -- calculated field, ensures integrity
);

ALTER TABLE SubclassB
ADD CONSTRAINT [FK_SubclassB_BaseClass]
FOREIGN KEY (BaseClass_ID, ClassTypeID)
REFERENCES BaseClass (BaseClass_ID, ClassTypeID);

This is how the database diagram looks in SQL Server 2012:

Database Diagram

Questions

  • Did I make any mistakes in my implementation?
  • Apart from making class type (in subclass tables) calculated and persisted, is there anything else I can do to prevent mistakes during INSERT/UPDATE/DELETE?

Best Answer

Yes, the design looks great. Minor notes:

  • You could use TINYINT, instead of the INT for the ClassTypeID. Or even CHAR(1) and have 'A' and 'B' instead of 1 and 2. 1 byte instead of 4 means you are saving 3 bytes in every row, in all 3 tables and in every index that includes ClassTypeID - which would be every index on these tables, if ClassTypeID is part of the clustered key.

  • The attributes of both the base and the subtype tables could be NOT NULL. I don't see why you would want them nullable, with this design.

  • It might be better (but needs thorough testing) if you had both the UNIQUE constraint of the base table and the 2 foreign keys that reference it defined with the reverse order (ClassTypeID, BaseClass_ID). This is more an indexing / physical design suggestion, it doesn't alter the logical design. I would experiment with having the clustered key in the base table using this order, too.

Related Question