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