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:
Attribute 1 | Attribute 2 | Attribute 3 | |
Row 1 | Value 11 | Value 21 | Value 31 |
Row 2 | Value 12 | Value 22 | Value 32 |
Row 3 | Value 13 | Value 23 | Value 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 1 | Attribute 2 | Attribute 3 | Attribute 4 | Attribute 5 | Attribute 6 | Attribute 7 | |
Row 1 | Value 11 | Value 21 | Value 31 | Value 41 | Value 51 | Value 61 | Value 71 |
Row 2 | Value 12 | Value 22 | Value 32 | Value 42 | Value 52 | Value 62 | Value 72 |
Row 3 | Value 13 | Value 23 | Value 33 | Value 43 | Value 53 | Value 63 | Value 73 |
Row 4 | Value 14 | Value 24 | Value 34 | Value 44 | Value 54 | Value 64 | Value 74 |
Row 5 | Value 15 | Value 25 | Value 35 | Value 45 | Value 55 | Value 65 | Value 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 :-)