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;

SELECT * FROM dbo.Attribute;

SELECT * FROM dbo.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:

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:

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 :-)