8

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

1 Answer 1

7

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.

5
  • 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) ? May 20, 2016 at 10:38
  • 4
    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). May 20, 2016 at 10:50
  • @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). Jun 7, 2016 at 17:47
  • 1
    @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... Jun 7, 2016 at 18:26
  • @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.
    – sujeet
    Jul 5, 2020 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.