snippetsqlMajor
How to model an entity type that can have different sets of attributes?
Viewed 0 times
cantypedifferentattributesthathowsetsmodelhaveentity
Problem
I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items.
This is pretty straightforward, yes; however, each Item belongs to a certain Category (e.g., a Car, a Boat or a Plane), and each Category has a particular number of attributes, e.g.:
Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.
Although it looked great at first, I couldn’t find a way to create the relationship between the Items and the Categories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database the table name and column.
In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a User may have with one query.
I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal.
Additional information
These are my responses to MDCCL comments:
In fact, it’s very simple: There are only five Categories in total.
This is pretty straightforward, yes; however, each Item belongs to a certain Category (e.g., a Car, a Boat or a Plane), and each Category has a particular number of attributes, e.g.:
Car structure:+----+--------------+--------------+
| PK | Attribute #1 | Attribute #2 |
+----+--------------+--------------+Boat structure:+----+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 |
+----+--------------+--------------+--------------+Plane structure:+----+--------------+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 |
+----+--------------+--------------+--------------+--------------+Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.
Although it looked great at first, I couldn’t find a way to create the relationship between the Items and the Categories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database the table name and column.
In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a User may have with one query.
I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal.
Additional information
These are my responses to MDCCL comments:
- How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?
In fact, it’s very simple: There are only five Categories in total.
- Will the same Item always belong to the same User (that is, once a given Item has be
Solution
According to your description of the business environment under consideration, there exists a supertype-subtype structure that encompasses Item —the supertype— and each of its Categories, i.e., Car, Boat and Plane (along with two more that were not made known) —the subtypes—.
I will detail below the method I would employ to manage said scenario.
Business rules
In order to start delineating the relevant conceptual schema, some of the most important business rules determined so far (restricting the analysis to the three disclosed Categories only, to keep things as brief as possible) can be formulated as follows:
Illustrative IDEF1X diagram
Figure 1 displays an IDEF1X1 diagram that I created to group the previous formulations along with other business rules that appear pertinent:
Supertype
On the one hand, Item, the supertype, presents the properties† or attributes that are common to all the Categories, i.e.,
Subtypes
On the other hand, the properties‡ that pertain to every particular Category, i.e.,
are shown in the corresponding subtype box.
Identifiers
Then, the Item.ItemId PRIMARY KEY (PK) has migrated3 to the subtypes with different role names, i.e.,
Mutually exclusive associations
As depicted, there is an association or relationship with a one-to-one (1:1) cardinality ratio between (a) each supertype occurrence and (b) its complementary subtype instance.
The exclusive subtype symbol portrays the fact that the subtypes are mutually exclusive, i.e., a concrete Item occurrence can be supplemented by a single subtype instance only: either one Car, or one Plane, or one Boat (never by zero or less, nor by two or more).
†, ‡ I employed classic placeholder names to entitle some of the entity type properties, as their actual denominations were not supplied in the question.
Expository logical-level layout
Consequently, in order to discuss an expository logical design, I derived the following SQL-DDL statements based on the IDEF1X diagram displayed 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(40) NOT NULL,
Baz CHAR(55) 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)
I will detail below the method I would employ to manage said scenario.
Business rules
In order to start delineating the relevant conceptual schema, some of the most important business rules determined so far (restricting the analysis to the three disclosed Categories only, to keep things as brief as possible) can be formulated as follows:
- A User owns zero-one-or-many Items.
- An Item is owned by exactly-one User at a specific instant.
- An Item may be owned by one-to-many Users at distinct points in time.
- An Item is classified by exactly-one Category.
- An Item is, at all times,
- either a Car
- or a Boat
- or a Plane.
Illustrative IDEF1X diagram
Figure 1 displays an IDEF1X1 diagram that I created to group the previous formulations along with other business rules that appear pertinent:
Supertype
On the one hand, Item, the supertype, presents the properties† or attributes that are common to all the Categories, i.e.,
- CategoryCode —specified as a FOREIGN KEY (FK) that references Category.CategoryCode and functions as a subtype discriminator, i.e., it indicates the exact Category of subtype with which a given Item must be connected—,
- OwnerId —distinguished as a FK that points to User.UserId, but I assigned it a role name2 in order to reflect its special implications more accurately—,
- Foo,
- Bar,
- Baz and
- CreatedDateTime.
Subtypes
On the other hand, the properties‡ that pertain to every particular Category, i.e.,
- Qux and Corge;
- Grault, Garply and Plugh;
- Xyzzy, Thud, Wibble and Flob;
are shown in the corresponding subtype box.
Identifiers
Then, the Item.ItemId PRIMARY KEY (PK) has migrated3 to the subtypes with different role names, i.e.,
- CarId,
- BoatId and
- PlaneId.
Mutually exclusive associations
As depicted, there is an association or relationship with a one-to-one (1:1) cardinality ratio between (a) each supertype occurrence and (b) its complementary subtype instance.
The exclusive subtype symbol portrays the fact that the subtypes are mutually exclusive, i.e., a concrete Item occurrence can be supplemented by a single subtype instance only: either one Car, or one Plane, or one Boat (never by zero or less, nor by two or more).
†, ‡ I employed classic placeholder names to entitle some of the entity type properties, as their actual denominations were not supplied in the question.
Expository logical-level layout
Consequently, in order to discuss an expository logical design, I derived the following SQL-DDL statements based on the IDEF1X diagram displayed 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(40) NOT NULL,
Baz CHAR(55) 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)
Code Snippets
-- 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(40) NOT NULL,
Baz CHAR(55) 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 DATE NOT NULL,
Corge DECIMAL(5,2) NOT NULL,
--
CONSTRAINT Car_PK PRIMARY KEY (CarId),
CONSTRAINT Car_to_Item_FK FOREIGN KEY (CarId)
REFERENCES Item (ItemId),
CONSTRAINT ValidQux_CK CHECK (Qux >= '1990-01-01')
);
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 SMALLINT NOT NULL,
Garply DATETIME NOT NULL,
Plugh CHAR(63) NOT NULL,
--
CONSTRAINT Boat_PK PRIMARY KEY (BoatId),
CONSTRAINT Boat_to_Item_FK FOREIGN KEY (BoatId)
REFERENCES Item (ItemId),
CONSTRAINT ValidGrault_CK CHECK (Grault <= 10000)
);
CREATE TABLE Plane ( -- Denotes one of the subtypes.
PlaneId INT NOT NULL, -- Must be co--
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', 'Motorway', 'Tire', 'Chauffeur', CURDATE());
-- 1.2
INSERT INTO Car
(CarId, Qux, Corge)
VALUES
(1, '1999-06-11', 999.99);
-- 2. ‘Full’ Boat INSERTion
-- 2.1
INSERT INTO Item
(ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
(2, 2, 'B', 'Ocean', 'Anchor', 'Sailor', CURDATE());
-- 2.2
INSERT INTO Boat
(BoatId, Grault, Garply, Plugh)
VALUES
(2, 10000, '2016-03-09 07:32:04.000', 'So far so good.');
-- 3 ‘Full’ Plane INSERTion
-- 3.1
INSERT INTO Item
(ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
(3, 3, 'P', 'Sky', 'Wing', 'Aviator', CURDATE());
-- 3.2
INSERT INTO Plane
(PlaneId, Xyzzy, Thud, Wibble, Flob)
VALUES
(3, 3258594758, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut sollicitudin pharetra sem id elementum. Sed tempor hendrerit orci. Ut scelerisque pretium diam, eu sodales ante sagittis ut. Phasellus id nunc commodo, sagittis urna vitae, auctor ex. Duis elit tellus, pharetra sed ipsum sit amet, bibendum dapibus mauris. Morbi condimentum laoreet justo, quis auctor leo rutrum eu. Sed id nibh non leo sodales pulvinar. Nam ornare ipsum nunc, eget molestie nulla ultrices vel. Curabitur fermentum nisl quis lorem aliquam pretium aliquam at mauris. In vestibulum, tellus et pharetra sollicitudin, mi lacus consectetur dolor, id volutpat nulla eros a mauris. ', 'Here we go!', TRUE);
----
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;
----
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;
--Context
StackExchange Database Administrators Q#149904, answer score: 24
Revisions (0)
No revisions yet.