2

I am creating a small database using SQL Server Express 2017.

  • There are two animals (Foxes and Elephants).
  • The Foxes table will have the columns: Identifier, Species, BodyLength, and FurColour.
  • The Elephant table will have: Identifier, Species, ElephantWeightKG, and TuskLengthCM.
  • Each animal will have a list of "Items", I have an Items table from which Foxes and Elephants both share.
  • Currently, there is no overlap between the Fox and Elephant identifiers. But there is the possibility in the future that the format of the Identifiers change (worst case scenario there may be a bit of overlap). The database should be designed to deal with this.

After a bit of trial and error, and research, this is what I have come up with:

create table Foxes
(
    Identifier varchar(50) not null constraint PK_FoxIdentifier primary key,
    Species varchar(50),
    BodyLength int,
    FurColour varchar(50),
);

create table Elephants
(
    Identifier varchar(50) not null constraint PK_ElephantsIdentifier primary key,
    Species varchar(50),
    ElephantWeightKG int,
    TuskLengthCM int,
);

create table Items
(
    ItemSeq int identity(1,1),
    ItemID as isnull(FoxID, ElephantID) + '-' + cast(ItemSeq as varchar) persisted primary key,
    FoxID varchar(50),
    ElephantID varchar(50),
    ItemDescription varchar(500),

    foreign key (FoxID) references Foxes(Identifier) on delete cascade on update cascade,
    foreign key (ElephantID) references Elephants(Identifier) on delete cascade on update cascade,

    constraint CK_FoxIDElephantsItemIdentifier check
    (
        case when FoxID is null then 0 else 1 end +
        case when ElephantID is null then 0 else 1 end = 1
    )
);

Is this an acceptable design? What would you do differently?

Thanks.

MDCCL
  • 8,530
  • 3
  • 32
  • 63

1 Answers1

4

Analyzing the scenario from a pure conceptual perspective, an important entity type that has been missrepresented so far is Animal, which would help to (a) convey all the relevant associations more accurately and to (b) delineate the whole database more clearly at the logical level.

In this way:

  • Fox and Elephant would then be entity subtypes of Animal, which would in turn be their entity supertype.

  • Since each particular Animal instance is either a Fox or an Elephant —never both— it means that the subtype instances are mutually exclusive.

  • Any given Animal occurrence —be it a Fox or an Elephant— may be linked to zero, one, or more Item occurrences, while an Item instance will be linked to exactly one Animal counterpart —be it a Fox or an Elephant—.

Note: Supertype-subtype associations are referred to as superclass/subclass relationships in some conceptual modelling methodologies.

Expository logical DDL design

Consequently, I would set up the corresponding logical-level design along the lines of:

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns depending
-- on the applicable business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient physical-level index strategies.


-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions.


CREATE TABLE Species ( 
    SpeciesCode CHAR(1)  NOT NULL, 
    Name        CHAR(30) NOT NULL, 
    --

    CONSTRAINT Species_PK PRIMARY KEY (SpeciesCode),
    CONSTRAINT Species_AK UNIQUE      (Name)

);


-- “Populating” the Species table:
INSERT INTO Species
    (SpeciesCode, Name)
VALUES
    ('F', 'Fox'),
    ('E', 'Elephant');


CREATE TABLE Animal ( -- Represents the supertype.
    AnimalId        INT       NOT NULL IDENTITY (1,1), -- Column meant to enclosed the Identifiers as established in the business context of relevance.
    SpeciesCode     CHAR(1)   NOT NULL, --Stands for the discriminator property.
    CreatedDateTime DATETIME  NOT NULL,

    --
    CONSTRAINT Animal_PK            PRIMARY KEY (AnimalId),
    CONSTRAINT Animal_to_Species_FK FOREIGN KEY (SpeciesCode)
        REFERENCES Species (SpeciesCode)
);


CREATE TABLE Fox ( -- Conveys one of the subtypes.
    FoxId      INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column.
    BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
    BodyLength INT         NOT NULL,
    FurColour  VARCHAR     NOT NULL,
    --
    CONSTRAINT Fox_PK           PRIMARY KEY (FoxId),
    CONSTRAINT Fox_AK           UNIQUE      (BusinessId), 
    CONSTRAINT Fox_to_Animal_FK FOREIGN KEY (FoxId)
        REFERENCES Animal (AnimalId)
);


CREATE TABLE Elephant ( -- Represents the other subtype.
    ElephantId INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column either.
    BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
    Weight     INT         NOT NULL,
    TuskLength INT         NOT NULL, 
    --
    CONSTRAINT Elephant_PK           PRIMARY KEY (ElephantId),
    CONSTRAINT Elephant_AK           UNIQUE      (BusinessId), 
    CONSTRAINT Elephant_to_Animal_FK FOREIGN KEY (ElephantId)
        REFERENCES Animal (AnimalId)
);


CREATE TABLE Item ( 
    ItemSeq         INT          NOT NULL IDENTITY (1,1), 
    AnimalId        INT          NOT NULL,
    Description     VARCHAR(500) NOT NULL, 
    CreatedDateTime DATETIME     NOT NULL,

    --
    CONSTRAINT Item_PK           PRIMARY KEY (ItemSeq),
    CONSTRAINT Item_to_Animal_FK FOREIGN KEY (AnimalId)
        REFERENCES Animal (AnimalId)

);

This db<>fiddle contains the DDL layout shown above so that you can see it in action.

The columns FoxId and ElephantId, constrained as the PKs of the appropriate tables, help in representing the conceptual-level one-to-one association by way of FK constraints that point to the AnimalId column, which is constrained as the PK of the Animal table. This implies that, in an actual “pair”, both the supertype and the subtype rows are identified by the same PK value; thus, it is more than opportune to mention that (a) attaching an extra column to hold system-controlled surrogate values to (b) the tables standing for the subtypes is (c) entirely superfluous.

As you can see I added the Animal.CreatedDateTime because I esteem that it is important to know the exact point in time when a certain Animal row was INSERTed into the database.

In this manner, the association between the Item rows and the Fox or Elephant rows is established “indirectly” via the corresponding Animal rows and the suitable constraints.

The Item.AnimalId column prevents having (1) one column for The Items.FoxId and (2) one column for The Items.ElephantId, with all the ambiguities and needless complexity they involve (e.g., accepting NULL marks with the repercussions they have on data manipulation, adding a CHECK constraint like the one you added to your Items table, etc.).

“Look-up” table for the FurColour data

I am not familiar with FurColours of Foxes (e.g., I do not know how many FurColours are presented by Fox specimens, or how the FurColours are composed, etc.), but you may like to evaluate incorporating a table that serves a “look-up” role for this sort of information, which can in turn be referenced to from a FOREIGN KEY constraint in the Fox table.

Integrity and consistency considerations

It is paramount to mention that, in your business context, you have to (1) ensure that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart, and, in turn, (2) guarantee that said “subtype” row is compatible with the value contained in the “discriminator” column of the “supertype” row.

It would be very elegant to enforce such circumstances in a declarative manner by virtue of ASSERTIONs but, unfortunately, none of the major SQL platforms has suitable support for these powerful instruments (which are the proper tools for this type of job). Therefore, resorting to procedural code within ACID TRANSACTIONs is quite convenient so that these conditions are always met in your database. Other option would be employing TRIGGERs (procedural as well), but they tend to make things untidy.

Creating views to fix useful derivable tables

It seems that one of the purposes served by the columns FoxID and ElephantID of your Items table is that they help to determine the type of Animal row that an Item row is linked to, but that need is better coped with by means of a view (i.e., a derived table) as exemplified as follows:

CREATE VIEW ItemWithAnimal AS
    SELECT I.ItemSeq,
           A.AnimalId,
           A.SpeciesCode,
           I.Description,
           I.CreatedDateTime AS ItemCreatedDateTime,
           A.CreatedDateTime AS AnimalCreatedDateTime
        FROM Item   I
        JOIN Animal A
          ON I.AnimalId = A.AnimalId;

…where the SpeciesCode column values indicate the exact species of the AnimalId value of concern, so the people interpreting the data can see if the Item under consideration is linked to a Fox or an Animal. So when you want to retrieve Item information, you can SELECT directly FROM this derived table or view, instead of the Item base table.

Having views that fetch the “full” Fox or Elephant information seems to be useful too, e.g.:

CREATE VIEW FullFox AS
    SELECT A.AnimalId,
           F.BusinessId,
           F.BodyLength,
           F.FurColor,
           A.CreatedDateTime
        FROM Fox    F
        JOIN Animal A
          ON F.FoxId = A.AnimalId;

Evidently, this view would be more benefitial if, say, the Animal table was comprised of more columns meant to be “shared” by the Fox or Elephant tables, but it is worth to illustrate the benefit of views.

The views discussed above are also included in the db<>fiddle previously linked to.

Review of your Items.ItemID, Items.FoxID and Items.ElephantID columns

It seems that your Items.ItemID column is some sort of derivable column, i.e., the values it contains are calculated in terms of the values contained in two other columns of your table, i.e., Items.FoxID or Items.ElephanID along with Items.ItemSeq (which is unnecessarily involved). I highly advise that you avoid resorting to that kind of course of action for any kind of column, but since Items.ItemID is constrained as the PRIMARY KEY (PK) I put more emphasis to my advise.

In this way, if in your business domain an Item is uniquely diferrentiated by the combination of its ItemSeq and the Identifier of the Animal of relevance (be it a Fox or an Elephant), you can then adapt the logical arrangement I expounded for the Item table by declaring a two-column composite primary key on (AnimalId, ItemSeq) and avoid the work arround with the derivable PK.

Other scenarios involving supertype-subtype associations

In case that you want to see more examples of supertype-subtype relationships, which are typical data structures that come about in business environments of very different natures, you may find of interest, e.g., my answers to the questions entitled:

You may like to take a look at the rest of posts grouped by the tag too.

Community
  • 1
MDCCL
  • 8,530
  • 3
  • 32
  • 63