Tag: model

According to your description of the business environment considered, there is a supertype subtype structure that encompasses article -The supertype- and each of its Categories, that is to say., Car, Boat and Plane (as well as two other unknowns) – the subtypes -.

I will detail below the method I would follow to manage such a scenario.

In order to start delineating the relevant areas conceptual schema, some of the most important trade rules determined so far (by limiting the analysis to the three Categories only, so that things are as brief as possible) can be formulated as follows:

• A User has zero-one-or-more goods
• A article belongs to exactly one User at a specific moment
• A article can belong to one or more users at different times
• A article is ranked by exactly one Category
• A article is, at any time,
  • be a Car
  • or one Boat
  • or one Plane

Figure 1 shows an IDEF1X¹ diagram that I have created to group previous formulations with other management rules that seem relevant:

supertype

From one side, article, the supertype, presents the properties^† or attributes that are common to all Categories, that is to say.,

• CatégorieCode – specified as a foreign key (FK) which reference Catégorie.CatégorieCode and works as a subtype discriminatorthat is to say that it indicates the exact Category subtype with which a given article must be connected-,
• ownerid "Distinguished as an FK pointing to User.UserIdbut I assigned him a role name² to more accurately reflect its particular implications,
• foo,
• Bar,
• Baz and
• CreatedDateTime.

Subtypes

On the other hand, the properties^‡ that relate to each particular Category, that is to say.,

• qux and Corge;
• Grault, Garply and Pugh;
• xyzzy, Thud, Nibble and Flob;

are displayed in the corresponding subtype area.

Identifiers

Then Item.ItemId PRIMARY KEY (PK) migrated³ subtypes with different role names, that is,

• carID,
• BoatId and
• PlaneId.

Mutually exclusive associations

As illustrated, there is an association or relationship of cardinality One by one (1: 1) between (a) each super-type occurrence and (b) its complementary subtype instance.

the exclusive subtype This symbol represents the fact that the subtypes are mutually exclusive, that is, one article This occurrence can only be completed by one subtype: one or the other. Car, or a Plane, or a Boat (never by two or more).

_{^{†, ‡} I've used classic reservation settings names to assign rights to certain entity type properties, their actual names not being provided in the question.}

Therefore, in order to discuss a logical repository design, I derive the following SQL-DDL statements based on the displayed IDEF1X diagram and described above:

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

-- Also, you should make accurate tests to define the 
-- most convenient INDEX strategies based on the exact 
-- data manipulation tendencies of your business context.

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

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE Category (
    CategoryCode     CHAR(1)  NOT NULL, -- Meant to contain meaningful, short and stable values, e.g.; 'C' for 'Car'; 'B' for 'Boat'; 'P' for 'Plane'.
    Name             CHAR(30) NOT NULL,
    --
    CONSTRAINT Category_PK PRIMARY KEY (CategoryCode),
    CONSTRAINT Category_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Item ( -- Stands for the supertype.
    ItemId           INT      NOT NULL,
    OwnerId          INT      NOT NULL,
    CategoryCode     CHAR(1)  NOT NULL, -- Denotes the subtype discriminator.
    Foo              CHAR(30) NOT NULL,
    Bar              CHAR(30) NOT NULL,
    Baz              CHAR(30) NOT NULL,  
    CreatedDateTime  DATETIME NOT NULL,
    --
    CONSTRAINT Item_PK             PRIMARY KEY (ItemId),
    CONSTRAINT Item_to_Category_FK FOREIGN KEY (CategoryCode)
        REFERENCES Category    (CategoryCode),
    CONSTRAINT Item_to_User_FK     FOREIGN KEY (OwnerId)
        REFERENCES UserProfile (UserId)  
);

CREATE TABLE Car ( -- Represents one of the subtypes.
    CarId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Qux   CHAR(30) NOT NULL,
    Corge CHAR(30) NOT NULL,   
    --
    CONSTRAINT Car_PK         PRIMARY KEY (CarId),
    CONSTRAINT Car_to_Item_FK FOREIGN KEY (CarId)
        REFERENCES Item (ItemId)  
);

CREATE TABLE Boat ( -- Stands for one of the subtypes.
    BoatId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Grault CHAR(30) NOT NULL,
    Garply CHAR(30) NOT NULL,   
    Plugh  CHAR(30) NOT NULL, 
    --
    CONSTRAINT Boat_PK         PRIMARY KEY (BoatId),
    CONSTRAINT Boat_to_Item_FK FOREIGN KEY (BoatId)
        REFERENCES Item (ItemId)  
);

CREATE TABLE Plane ( -- Denotes one of the subtypes.
    PlaneId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Xyzzy   CHAR(30) NOT NULL,
    Thud    CHAR(30) NOT NULL,  
    Wibble  CHAR(30) NOT NULL,
    Flob    CHAR(30) NOT NULL,  
    --
    CONSTRAINT Plane_PK         PRIMARY KEY (PlaneId),
    CONSTRAINT Plane_to_Item_PK FOREIGN KEY (PlaneId)
        REFERENCES Item (ItemId)  
);

As demonstrated, the type of superentity and each of the types of sub-entities are represented by the based table.

The columns CarId, BoatId and PlaneId, constrained as PK of the appropriate tables, help to represent the one-to-one conceptual level association by means of FK constraints^§ this point to the ItemId column, which is constrained as PK of the Item table. This means that in a real "pair", the supertype and subtype lines are identified by the same PK value; it is therefore more than timely to mention that

• (a) attach a additional column to hold the system-controlled substitution values^‖ to (b) the tables corresponding to the subtypes are (c) completely superfluous.

_{^§ To avoid problems and errors concerning (especially the words FOREIGN) the KEY constraint definitions (the location you referred to in the comments), it is very important to take into account dependence on existence this happens between the different tables available, as shown in the statement order of the DDL structure tables of the exponent, which I have also provided in this SQL Fiddle.}

_{^‖ For example, adding an additional column with the AUTO_INCREMENT property to a table of a database built on MySQL.}

Integrity and consistency considerations

It is important to note that in your work environment, you must (1) ensure that each row of "supertype" is at any time completed by its corresponding "subtype" counterpart, and, in turn, (2) guarantee that the "subtype" line is compatible with the value contained in the "discriminator" column of the "supertype" line.

It would be very elegant to apply such circumstances in a declarative as far as I know, unfortunately, none of the big SQL platforms has provided the proper mechanisms to do this. Therefore, using the procedure code in ACID TRANSACTIONS is very convenient so that these conditions are always met in your database. Another option would be to use TRIGGERS, but they tend to make things messy, so to speak.

Having a logical design like the one explained above, it would be very convenient to create one or more views, ie. derivative tables that include columns belonging to two or more of the relevant elements. based the tables. In this way, you can, for example, select directly from these views without having to write all the JOINs each time you have to retrieve "combined" information.

Sample of data

In this regard, assume that the base tables are "populated" with the sample data presented below:

--

INSERT INTO UserProfile 
    (UserId, FirstName, LastName, BirthDate, GenderCode, Username, CreatedDateTime)
VALUES
    (1, 'Edgar', 'Codd', '1923-08-19', 'M', 'ted.codd', CURDATE()),
    (2, 'Michelangelo', 'Buonarroti', '1475-03-06', 'M', 'michelangelo', CURDATE()),
    (3, 'Diego', 'Velázquez', '1599-06-06', 'M', 'd.velazquez', CURDATE());

INSERT INTO Category 
    (CategoryCode, Name)
VALUES
    ('C', 'Car'), ('B', 'Boat'), ('P', 'Plane');

-- 1. ‘Full’ Car INSERTion

-- 1.1 
INSERT INTO Item
    (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
    (1, 1, 'C', 'This datum', 'That datum', 'Other datum', CURDATE());

 -- 1.2
INSERT INTO Car
    (CarId, Qux, Corge)
VALUES
    (1, 'Fantastic Car', 'Powerful engine pre-update!');

-- 2. ‘Full’ Boat INSERTion

-- 2.1
INSERT INTO Item
  (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
  (2, 2, 'B', 'This datum', 'That datum', 'Other datum', CURDATE());

-- 2.2
INSERT INTO Boat
    (BoatId, Grault, Garply, Plugh)
VALUES
    (2, 'Excellent boat', 'Use it to sail', 'Everyday!');

-- 3 ‘Full’ Plane INSERTion

-- 3.1
INSERT INTO Item
  (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
  (3, 3, 'P', 'This datum', 'That datum', 'Other datum', CURDATE());

-- 3.2
INSERT INTO Plane
    (PlaneId, Xyzzy, Thud, Wibble, Flob)
VALUES
    (3, 'Extraordinary plane', 'Traverses the sky', 'Free', 'Like a bird!');

--

Then, an advantageous view is that which groups the columns of Item, Car and UserProfile:

--

CREATE VIEW CarAndOwner AS
    SELECT C.CarId,
           I.Foo,
           I.Bar,
           I.Baz,
           C.Qux,
           C.Corge,           
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Car C
          ON C.CarId = I.ItemId
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;

--

Of course, a similar approach can be followed so that you can also SELECT the Boat and Plane information directly from one table (a derivative, in these cases).

After that – if the presence of NULL marks in the result sets does not bother you – with the following VIEW definition, you can, for example, "collect" columns from the tables. Item, Car, Boat, Plane and UserProfile:

--

CREATE VIEW FullItemAndOwner AS
    SELECT I.ItemId,
           I.Foo, -- Common to all Categories.
           I.Bar, -- Common to all Categories.
           I.Baz, -- Common to all Categories.
          IC.Name      AS Category,
           C.Qux,    -- Applies to Cars only.
           C.Corge,  -- Applies to Cars only.
           --
           B.Grault, -- Applies to Boats only.
           B.Garply, -- Applies to Boats only.
           B.Plugh,  -- Applies to Boats only.
           --
           P.Xyzzy,  -- Applies to Planes only.
           P.Thud,   -- Applies to Planes only.
           P.Wibble, -- Applies to Planes only.
           P.Flob,   -- Applies to Planes only.
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Category IC
          ON I.CategoryCode = IC.CategoryCode
   LEFT JOIN Car C
          ON C.CarId = I.ItemId
   LEFT JOIN Boat B
          ON B.BoatId = I.ItemId
   LEFT JOIN Plane P
          ON P.PlaneId = I.ItemId               
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;

--

The code of views presented here is only illustrative. Of course, some test drills and modifications can help speed up the (physical) execution of queries. In addition, you may have to delete or add columns to these views based on the needs of the business.

The sample data and all view definitions are embedded in this SQL Fiddle so that they can be observed in action.

Manipulation of data: application program code and column alias

The use of application program code (if that is what you mean by "server-specific code") and column aliases are other important points that you have mentioned in the next comments:

• I've managed to work around (a joint) problem with server-specific code, but I really do not want to do that-and-adding aliases to all columns could be "stressful" .

• Very well explained, thank you very much. However, as I suspected, I will have to manipulate the result set by listing all the data because of the similarities with some columns because I do not want to use multiple aliases to keep the instruction cleaner.

It is appropriate to indicate that if the use of the application program code is a very useful resource to manage presentation features (or graphics) sets of results, it is essential to avoid data recovery line by line to avoid speed problems. The goal should be to "recover" the relevant datasets in their entirety using the robust data manipulation tools provided by the (precisely) SQL platform engine to optimize the behavior of your system.

In addition, using aliases to rename one or more columns to a certain extent may seem stressful, but I personally consider this resource a very powerful tool that allows (i) contextualizing and (ii) disambiguating the meaning and intention assigned to the columns concerned; therefore, this is an aspect that needs to be carefully considered in the handling of data of interest.

You might as well find help for this series of publications and this group of publications which contains my opinion on two other cases that include supertype associations with mutually exclusive subtypes.

I've also proposed a solution for a professional environment involving a supertype type cluster where the subtypes are do not mutually exclusive in this (more recent) answer.

---

_{¹Definition of integration for information modeling (IDEF1X) is a highly recommended data modeling technique, established as a Standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is firmly based on (a) some of the theoretical work written by the single origin the relational model, namely Dr. E. F. Codd; on (b) the entity-relationship vision, developed by Dr. P. P. Chen; and also on (c) the logical database design technique, created by Robert G. Brown.}

_{² In IDEF1X, a role name is a distinctive label assigned to an FK property (or attribute) to express its meaning in the field of its respective entity type.}

_{³ The IDEF1X standard defines key migration as "The modeling process of placing the primary key of a parent or generic entity in its child entity or category as a foreign key".}