According to my interpretation of your specifications, you want to find a method to implement two different (but connected) supertype-subtype structures.
In order to expose an approach to achieve the aformentioned task, I am going to add to the scenario at issue the two classic hypothetical entity types called Foo and Bar, which I will detail bellow.
Business rules
Here are a few statements that will help me to create a logical model:
A Foo is either one Bar or one CA Foo is categorized by one FooTypeA Bar is either one A or one CA Bar is classified by one BarType
Logical model
And then, the resulting IDEF1X[1] logical model is shown in Figure 1 (and you can download it from Dropbox as a PDF, as well):
The Foo and Bar addition
I did not add Foo and Bar to make the model look better, but to make it more expressive. I deem they are important due to the following:
As A and B share the attribute named E, this feature suggests that they are subentity types of a distinct (but related) sort of concept, event, person, measurement, etc., which I represented by means of the Bar superentity type that, in turn, is a subentity type of Foo, which holds the D attribute at the top of the hierarchy.
Since C only shares one attribute with the rest of the entity types under discussion, i.e., D, this aspect insinuates that it is a subentity type of another kind of concept, event, person, measurement, etc., so I depicted this circumstance by virtue of the Foo super entity type.
However, these are just assumptions, and since a relational database is meant to reflect the semantics of a certain business context accurately, you have to identify and classify all the things of interest in your specific domain so that you can, precisely, capture more meaning.
Important factors at the design phase
It is quite useful to be aware of the fact that, putting all the terminology aside, an exclusive supertype-subtype cluster is an ordinary relationship. Let us describe the situation in the following way:
- Each exclusive superentity type occurrence is related to only one subentity type complement.
Thus, there is a correspondance (or cardinality) of one-to-one (1:1) in these cases.
As you know from your preceding posts, the discriminator attribute (column, when implemented) plays a paramount role when creating an association of this nature, because it indicates the correct subtype instance with which the supertype is connected. The migration of the PRIMARY KEY from (i) the supertype to (ii) the subtypes is also of prime significance.
Concrete DDL structure
And then I wrote a DDL structure that is based on the logical model presented above:
CREATE TABLE FooType -- Look-up table.
(
FooTypeCode CHAR(2) NOT NULL,
Description CHAR(90) NOT NULL,
CreatedDateTime DATETIME NOT NULL,
CONSTRAINT PK_FooType PRIMARY KEY (FooTypeCode),
CONSTRAINT AK_FooType_Description UNIQUE (Description)
);
CREATE TABLE Foo -- Supertype
(
FooId INT NOT NULL, -- This PK migrates (1) to ‘Bar’ as ‘BarId’, (2) to ‘A’ as ‘AId’, (3) to ‘B’ as ‘BId’, and (4) to ‘C’ as ‘CId’.
FooTypeCode CHAR(2) NOT NULL, -- Discriminator column.
D INT NOT NULL, -- Column that applies to ‘Bar’ (and therefore to ‘A’ and ‘B’) and ‘C’.
CreatedDateTime DATETIME NOT NULL,
CONSTRAINT PK_Foo PRIMARY KEY (FooId),
CONSTRAINT FK_from_Foo_to_FooType FOREIGN KEY (FooTypeCode)
REFERENCES FooType (FooTypeCode)
);
CREATE TABLE BarType -- Look-up table.
(
BarTypeCode CHAR(1) NOT NULL,
Description CHAR(90) NOT NULL,
CONSTRAINT PK_BarType PRIMARY KEY (BarTypeCode),
CONSTRAINT AK_BarType_Description UNIQUE (Description)
);
CREATE TABLE Bar -- Subtype of ‘Foo’.
(
BarId INT NOT NULL, -- PK and FK.
BarTypeCode CHAR(1) NOT NULL, -- Discriminator column.
E INT NOT NULL, -- Column that applies to ‘A’ and ‘B’.
CONSTRAINT PK_Bar PRIMARY KEY (BarId),
CONSTRAINT FK_from_Bar_to_Foo FOREIGN KEY (BarId)
REFERENCES Foo (FooId),
CONSTRAINT FK_from_Bar_to_BarType FOREIGN KEY (BarTypeCode)
REFERENCES BarType (BarTypeCode)
);
CREATE TABLE A -- Subtype of ‘Bar’.
(
AId INT NOT NULL, -- PK and FK.
X INT NOT NULL, -- Particular column.
CONSTRAINT PK_A PRIMARY KEY (AId),
CONSTRAINT FK_from_A_to_Bar FOREIGN KEY (AId)
REFERENCES Bar (BarId)
);
CREATE TABLE B -- (1) Subtype of ‘Bar’ and (2) supertype of ‘A’ and ‘B’.
(
BId INT NOT NULL, -- PK and FK.
Y INT NOT NULL, -- Particular column.
CONSTRAINT PK_B PRIMARY KEY (BId),
CONSTRAINT FK_from_B_to_Bar FOREIGN KEY (BId)
REFERENCES Bar (BarId)
);
CREATE TABLE C -- Subtype of ‘Foo’.
(
CId INT NOT NULL, -- PK and FK.
Z INT NOT NULL, -- Particular column.
CONSTRAINT PK_C PRIMARY KEY (CId),
CONSTRAINT FK_from_C_to_Foo FOREIGN KEY (FooId)
REFERENCES Foo (FooId)
);
With this structure you avoid the storage of NULL marks in your base tables (or relations), which would introduce ambiguity to your data base.
Integrity, consistency and other considerations
Once you are implementing your database, you must ensure that (a) each exclusive supertype row is always complemented by its corresponding subtype counterpart and, in turn, guarantee that (b) such subtype row is compatible with the value contained in the supertype discriminator column. Therefore, it is quite convenient to employ ACID TRANSACTIONS in order to make sure that these conditions are met in your database.
You should not give up the logical soundness, self-expressivity and accuracy of your database, these are aspects that decidedly make your database more solid.
The two previously posted answers already include pertinent points that are certainly worth taking into account when designing, creating and managing your database and its application program(s).
Retrieving data by way of VIEW definitions
You can set up some views that combine columns of the different supertype-subtype groups, so that you can retrieve the data at hand without, e.g., writing the necessary JOIN clauses every time. In this way, you can SELECT directly FROM the VIEW (a derived relation or table) of interest with ease.
As you can see, “Ted” Codd was, undoubtedly, a genius. The tools he bequeathed are quite strong and elegant, and, of course, are well integrated with each other.
Related resources
If you want to analyze some extensive database which involves supertype-subtype relationships, you would find of value the extraordinary answers proposed by @PerformanceDBA to the following Stack Overflow questions:
Note
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by Dr. E. F. Codd; on (b) the Entity-Relationship view of data, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that IDEF1X was formalized by way of first-order logic.
