Peter Lalovsky
Keep it simple
Peter Lalovsky
Keep it simple
Backend Data Model T-SQL

Data Model: Entity-Atribute-Value (EAV)

by |Published

Entity-Atribute-Value (EAV) data model gives us the flexibility to store the data in a way that we have dynamic number of:

  • tables
  • columns

in the simplest EAV we have 3 tables:

  • Entity
  • Attribute
  • Value

To simplify the understanding of the model we declare:

  • Table Entity holds the list of the tables in the database
  • Table Attribute holds the list of the columns in the tables
  • Tables Value hold the content of the cells in the tables

Tables in the database:

Data, extracted from the EAV model:

Database Diagram:

Once you start using the EAV model, you will expand it by adding more tables. In this example i add:

  • Table EntityAttribute. It is a M:M relation between tables Entity and Attribute and it’s goal is to be able to reuse the same attributes in many tables.
  • Multiple tables for Value to store the different data types separately.

Create tables:

USE [master];
GO

IF (DB_ID('EAV') IS NOT NULL)
  BEGIN
    ALTER DATABASE EAV SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE EAV;
  END
GO

CREATE DATABASE EAV;
GO

USE EAV;
GO

DROP TABLE IF EXISTS dbo.Entity
CREATE TABLE dbo.Entity -- Rows
(
  EntityID INT IDENTITY(1, 1) NOT NULL
  , EntityName NVARCHAR(128) NOT NULL
  , CONSTRAINT PK__dbo_Entity PRIMARY KEY (EntityID)
);
GO

DROP TABLE IF EXISTS dbo.Attribute
CREATE TABLE dbo.Attribute -- Columns
(
  AttributeID INT IDENTITY(1, 1) NOT NULL
  , AttributeName NVARCHAR(128) NOT NULL
  , CONSTRAINT PK__dbo_Attribute PRIMARY KEY (AttributeID)
);
GO

-- M:M Entity and Attribute
-- To be able to reuse the attributes
DROP TABLE IF EXISTS dbo.EntityAttribute
CREATE TABLE dbo.EntityAttribute -- Data
(
  EntityAttributeID INT IDENTITY(1, 1) NOT NULL
  , EntityID INT
  , AttributeID INT
  , CONSTRAINT PK__dbo_EntityAttribute PRIMARY KEY (EntityAttributeID)
  , CONSTRAINT FK__dbo_Entity__dbo_EntityAttribute FOREIGN KEY (EntityID) REFERENCES dbo.Entity (EntityID)
  , CONSTRAINT FK__dbo_Attribute__dbo_EntityAttribute FOREIGN KEY (AttributeID) REFERENCES dbo.Attribute (AttributeID)
);
GO

-- Value
DROP TABLE IF EXISTS dbo.ValueInteger
CREATE TABLE dbo.ValueInteger
(
  ValueIntegerID INT IDENTITY(1, 1)
  , RowID BIGINT
  , EntityAttributeID INT
  , [Value] INTEGER
  , CONSTRAINT PK__dbo_ValueInteger PRIMARY KEY (ValueIntegerID)
  , CONSTRAINT FK__dbo_EntityAttribute__dbo_ValueInteger
    FOREIGN KEY (EntityAttributeID)
    REFERENCES dbo.EntityAttribute (EntityAttributeID)
);
GO

DROP TABLE IF EXISTS dbo.ValueTextShort
CREATE TABLE dbo.ValueTextShort
(
  ValueTextShortID INT IDENTITY(1, 1)
  , RowID BIGINT
  , EntityAttributeID INT
  , [Value] NVARCHAR(128)
  , CONSTRAINT PK__dbo_ValueTextShort PRIMARY KEY (ValueTextShortID)
  , CONSTRAINT FK__dbo_EntityAttribute__dbo_ValueTextShort
    FOREIGN KEY (EntityAttributeID)
    REFERENCES dbo.EntityAttribute (EntityAttributeID)
);
GO

DROP TABLE IF EXISTS dbo.ValueTextLong
CREATE TABLE dbo.ValueTextLong
(
  ValueTextLongID INT IDENTITY(1, 1)
  , RowID BIGINT
  , EntityAttributeID INT
  , [Value] NVARCHAR(MAX)
  , CONSTRAINT PK__dbo_ValueTextLong PRIMARY KEY (ValueTextLongID)
  , CONSTRAINT FK__dbo_EntityAttribute__dbo_ValueTextLong
    FOREIGN KEY (EntityAttributeID)
    REFERENCES dbo.EntityAttribute (EntityAttributeID)
);
GO

DROP TABLE IF EXISTS dbo.ValueFloat
CREATE TABLE dbo.ValueFloat
(
  ValueFloatID INT IDENTITY(1, 1)
  , RowID BIGINT
  , EntityAttributeID INT
  , [Value] FLOAT
  , CONSTRAINT PK__dbo_ValueFloat PRIMARY KEY (ValueFloatID)
  , CONSTRAINT FK__dbo_EntityAttribute__dbo_ValueFloat
    FOREIGN KEY (EntityAttributeID)
    REFERENCES dbo.EntityAttribute (EntityAttributeID)
);
GO

DROP TABLE IF EXISTS dbo.ValueDateTime
CREATE TABLE dbo.ValueDateTime
(
  ValueDateTimeID INT IDENTITY(1, 1)
  , RowID BIGINT
  , EntityAttributeID INT
  , [Value] DATETIME
  , CONSTRAINT PK__dbo_ValueDateTime PRIMARY KEY (ValueDateTimeID)
  , CONSTRAINT FK__dbo_EntityAttribute__dbo_ValueDateTime
    FOREIGN KEY (EntityAttributeID)
    REFERENCES dbo.EntityAttribute (EntityAttributeID)
);
GO

DROP TABLE IF EXISTS dbo.ValueDate
CREATE TABLE dbo.ValueDate
(
  ValueDateID INT IDENTITY(1, 1)
  , RowID BIGINT
  , EntityAttributeID INT
  , [Value] DATE
  , CONSTRAINT PK__dbo_ValueDate PRIMARY KEY (ValueDateID)
  , CONSTRAINT FK__dbo_EntityAttribute__dbo_ValueDate
    FOREIGN KEY (EntityAttributeID)
    REFERENCES dbo.EntityAttribute (EntityAttributeID)
);
GO

Populate the tables:

USE EAV;
GO

INSERT dbo.Entity (EntityName)
SELECT           'Person'
UNION ALL SELECT 'Car';
GO

INSERT dbo.Attribute
SELECT           'FirstName'
UNION ALL SELECT 'LastName'
UNION ALL SELECT 'DateOfCreation'
UNION ALL SELECT 'Make'
UNION ALL SELECT 'Model'
UNION ALL SELECT 'Color'
UNION ALL SELECT 'HorsePower'
UNION ALL SELECT 'NumOfSeats'
UNION ALL SELECT 'BodyType';
GO

INSERT dbo.EntityAttribute (EntityID, AttributeID)
SELECT           1, 1
UNION ALL SELECT 1, 2
UNION ALL SELECT 1, 3
UNION ALL SELECT 2, 4
UNION ALL SELECT 2, 5
UNION ALL SELECT 2, 6
UNION ALL SELECT 2, 7
UNION ALL SELECT 2, 8
UNION ALL SELECT 2, 9
UNION ALL SELECT 2, 3;
GO

INSERT dbo.ValueTextShort (RowID, EntityAttributeID, [Value])
SELECT           1, 1, 'John'
UNION ALL SELECT 1, 2, 'Smith'
UNION ALL SELECT 2, 1, 'Adam'
UNION ALL SELECT 2, 2, 'Bentul'
UNION ALL SELECT 3, 1, 'Betty'
UNION ALL SELECT 3, 2, 'Larson'
UNION ALL SELECT 1, 4, 'BMW'
UNION ALL SELECT 1, 5, '325'
UNION ALL SELECT 1, 6, 'Red'
UNION ALL SELECT 1, 9, 'Cabrio'
UNION ALL SELECT 2, 4, 'Toyota'
UNION ALL SELECT 2, 5, 'Corolla'
UNION ALL SELECT 2, 6, 'Black'
UNION ALL SELECT 2, 9, 'Sedan'
UNION ALL SELECT 3, 4, 'Trabant'
UNION ALL SELECT 3, 5, '601 S'
UNION ALL SELECT 3, 6, 'Light Blue'
UNION ALL SELECT 3, 9, 'Universal'
UNION ALL SELECT 4, 4, 'Cadillac'
UNION ALL SELECT 4, 5, 'Eldorado'
UNION ALL SELECT 4, 6, 'Pink'
UNION ALL SELECT 4, 9, 'Cabrio'
UNION ALL SELECT 5, 4, 'Lada'
UNION ALL SELECT 5, 5, 'Niva'
UNION ALL SELECT 5, 6, 'White'
UNION ALL SELECT 5, 9, 'AWD';
GO

INSERT dbo.ValueDate (RowID, EntityAttributeID, [Value])
SELECT           1, 3, '1966-05-11'
UNION ALL SELECT 2, 3, '1961-12-04'
UNION ALL SELECT 3, 3, '1963-05-17'
UNION ALL SELECT 1, 10, '1969-03-16'
UNION ALL SELECT 2, 10, '1964-01-17'
UNION ALL SELECT 3, 10, '1967-10-09'
UNION ALL SELECT 4, 10, '1962-05-13'
UNION ALL SELECT 5, 10, '1964-09-03'
GO

INSERT dbo.ValueInteger (RowID, EntityAttributeID, [Value])
SELECT            1, 8, 2
UNION ALL SELECT 2, 8, 5
UNION ALL SELECT 3, 8, 4
UNION ALL SELECT 4, 8, 6
UNION ALL SELECT 5, 8, 4;
GO

INSERT dbo.ValueFloat (RowID, EntityAttributeID, [Value])
SELECT           1, 7, 214.7
UNION ALL SELECT 2, 7, 123.4
UNION ALL SELECT 3, 7, 26.0
UNION ALL SELECT 4, 7, 243.41
UNION ALL SELECT 5, 7, 75.736;
GO
SELECT * FROM dbo.Entity;
Entity
SELECT * FROM dbo.Attribute;
Attribute
SELECT * FROM dbo.EntityAttribute;
EntityAttribute

AttributeID = 3 is reused.

SELECT * FROM dbo.ValueDate;
SELECT * FROM dbo.ValueFloat;
SELECT * FROM dbo.ValueInteger;
SELECT * FROM dbo.ValueTextShort;

The first query will generate table (entity) Person:

Attribute 1Attribute 2Attribute 3
Row 1Value 11Value 21Value 31
Row 2Value 12Value 22Value 32
Row 3Value 13Value 23Value 33
DECLARE @EntityID SYSNAME;
SELECT @EntityID = EntityID FROM dbo.Entity WHERE EntityName = 'Person';
SELECT
  P.FirstName
  , P.LastName
  , P.DateOfCreation
FROM
  (
    SELECT
      V.RowID
      , A.AttributeName
      , V.[Value]
    FROM
      dbo.EntityAttribute AS EA
      JOIN dbo.Attribute  AS A  ON EA.AttributeID = A.AttributeID
      JOIN
      (
        SELECT            RowID, EntityAttributeID, CONVERT(CHAR(10), [Value], 120) AS [Value]  FROM dbo.ValueDate
        UNION ALL SELECT  RowID, EntityAttributeID, CONVERT(CHAR(23), [Value], 121)             FROM dbo.ValueDateTime
        UNION ALL SELECT  RowID, EntityAttributeID, FORMAT([Value], N'G')                       FROM dbo.ValueFloat
        UNION ALL SELECT  RowID, EntityAttributeID, CONVERT(CHAR(10), [Value])                  FROM dbo.ValueInteger
        UNION ALL SELECT  RowID, EntityAttributeID, [Value]                                     FROM dbo.ValueTextLong
        UNION ALL SELECT  RowID, EntityAttributeID, [Value]                                     FROM dbo.ValueTextShort
      )                   AS V  ON EA.EntityAttributeID = V.EntityAttributeID
    WHERE EA.EntityID = @EntityID
  ) AS S
  PIVOT
  (
    MAX([Value])
    FOR AttributeName IN ([FirstName], [LastName], [DateOfCreation])
  ) AS P
ORDER BY P.RowID;

The second query will generate table Car:

Attribute 1Attribute 2Attribute 3Attribute 4 Attribute 5 Attribute 6 Attribute 7
Row 1Value 11Value 21Value 31Value 41Value 51Value 61Value 71
Row 2Value 12Value 22Value 32Value 42Value 52Value 62Value 72
Row 3Value 13Value 23Value 33Value 43Value 53Value 63Value 73
Row 4 Value 14Value 24Value 34Value 44Value 54Value 64Value 74
Row 5 Value 15Value 25Value 35Value 45Value 55Value 65Value 75
DECLARE @EntityID SYSNAME;
SELECT @EntityID = EntityID FROM dbo.Entity WHERE EntityName = 'Car';

SELECT
  P.Make
  , P.Model
  , P.Color
  , P.HorsePower
  , P.NumOfSeats
  , P.BodyType
  , P.DateOfCreation
FROM
  (
    SELECT
      V.RowID
      , A.AttributeName
      , V.[Value]
    FROM
      dbo.EntityAttribute AS EA
      JOIN dbo.Attribute  AS A  ON EA.AttributeID = A.AttributeID
      JOIN
      (
        SELECT            RowID, EntityAttributeID, CONVERT(CHAR(10), [Value], 120) AS [Value]  FROM dbo.ValueDate
        UNION ALL SELECT  RowID, EntityAttributeID, CONVERT(CHAR(23), [Value], 121)             FROM dbo.ValueDateTime
        UNION ALL SELECT  RowID, EntityAttributeID, FORMAT([Value], N'G')                       FROM dbo.ValueFloat
        UNION ALL SELECT  RowID, EntityAttributeID, CONVERT(CHAR(10), [Value])                  FROM dbo.ValueInteger
        UNION ALL SELECT  RowID, EntityAttributeID, [Value]                                     FROM dbo.ValueTextLong
        UNION ALL SELECT  RowID, EntityAttributeID, [Value]                                     FROM dbo.ValueTextShort
      )                   AS V  ON EA.EntityAttributeID = V.EntityAttributeID
    WHERE EA.EntityID = @EntityID
  ) AS S
  PIVOT
  (
    MAX([Value])
    FOR AttributeName IN ([Make], [Model], [Color], [HorsePower], [NumOfSeats], [BodyType], [DateOfCreation])
  ) AS P
ORDER BY P.RowID;

By following the same logic, we can store as many tables with as many columns as we need.

In this example column DateOfCreation is reused in both tables.

When we use EAV for our application, we need to create a “configuration” or “definition” interface to give the users an option to configure their tables.

Keep it simple :-)

AUTHOR

Peter Lalovsky
Canada based bilingual data engineer and data architect with more than 20 years experience in IT, including the last 10 years in Data Engineering and Business Intelligence. Consultant and owner of zPL Concept Inc.

LinkedIn | GitHub

You may also like

Leave a comment

Your email address will not be published. Required fields are marked *

Time limit exceeded. Please complete the captcha once again.