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
B. In order to keep things simple, all their attributes will be of
A has following attributes:
B has following attributes:
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.
-- 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:
- 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