Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

Question

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?

Answer 1

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.

• AlwaysLearningNewStuff: Wow, so many useful suggestions... I have upvoted your answer. I will probably follow your advice and use char(1) instead of int as space preservation is high on my list. I will also go with your advice and make fields NOT NULL. I do not quite understand your 3rd point but would like to learn about it. Can you suggest some online resources (I apologize for asking, but I am software developer so I would need to learn more about your 3rd suggestion) ?
• ypercubeᵀᴹ: TINYINT is also 1 byte but CHAR(1) is more readable, when one is looking at the data. There is slight overhead though with using a char instead of an integer column (as collations/character sets have to be considered).
• Solomon Rutzky: @AlwaysLearningNewStuff Personally I would opt typically for TINYINT over CHAR(1). Though CHAR(1) can occasionally be useful, in which case you would want to set the column to a binary Collation, such as Latin1_General_100_BIN2, and use characters a-z and A-Z as I believe they are the same across all Code Pages (not 100% sure about the EBCDIC ones, though, but still).
• AlwaysLearningNewStuff: @srutzky: Thank you, I opted for TINYINT too. I have a problem with this case where (in my gut feeling), I should somehow optimize the subclasses that have common attribute E (please read the question, it is short and, in my humble opinion, concise). Thank you for the tip...
• sujeet: @AlwaysLearningNewStuff How would you perform insert queries for this design? Suppose If you want to insert a new row or rows in bulk in any of the subtype.