Entity Attribute Value Model

EAV (Entity-Attribute Value) Model

It is a data model to describe entities where number of attributes for the entities could be very large in amount.

EAV helps us to overcome the limitations of conventional database model.

Ex: Organization ‘ABC’ defines the entity ‘Address’ as:

Organization ‘ABC’: Address
Line1	Line2	State	Country	Zip

                

 At the same time Organization ‘XYZ’ may define ‘Address’ as:

Organization ‘XYZ’: Address
Line1	Line2	Line3	City	State	Country	Zip	Phone

A conventional database table design (one fact per column or set of facts per row) is unsuitable for such type of data, because of:

·        the need to continually add new tables or columns whenever new facts need incorporation

·        database vendor limitations on the number of columns per table

Therefore to deal with the dynamic nature of the data we make use of EAV model.

The Dynamic Data refers to the data whose structure doesn’t remains constant and keeps changing from organization to organization.

Understanding EAV model

 In this one each row stores the single fact about an entity. Whereas, in conventional model a row consist of each column per attribute and each column consist of a fact which means a set of facts in a row.

Model comprises of two main parts:

EAV metadata

            It stores data about data. The structure of any data entity is stored in metadata. The metadata changes as a modification is made in the data entity.

EAV data 

            It is the data in the entities.  It makes use of the metadata to relate different attributes of an entity. An EAV table constitutes of an entity, the attribute and the associated value of that attribute. Conceptually it’s a table with three columns.

Benefits of EAV representation:

·        Flexibility: No limits on number of attributes per entity. Hence, make the table or entity generic.

·        Space-efficient: Need not reserve space for attributes whose values are null.

Modeling dynamic data using EAV model


Ex:

EAV metadata tables

Entity types tables:

Entity_id	Name of Entity	Description	Label	TreeDepth
101	Investigator	Profile of Investigator	Null	0
102	Profile	Profile	Null	1
105	Profile Details	Profile Details	Null	2

Entity_Type_id	Name of Entity	Description	Label	TreeDepth
1	String	String Entity	Null
2	Date	Date Entity	Null
3	Number	Number Entity	Null
4	Boolean	Boolean Entity	Null
5	List of Values	Select Single value from a List	Null
6	Multi-Select	Select Multiple Value from a list	Null

Attribute table:

Attribute_id	Name of Attribute	Parent_id	Entity_Type_id
4	First Name	102	1
5	Prefix	102	5

EAV Data Tables (String_attribute)

Entity_id	Attribute_id	Value
102	4	Rishoo
102	5	Er.

Other Related Links:

EAV(Entity-Attribute Value) Model
EAV versus Row modeling
Downsides of EAV data model over class row model
Opting classic row modeling or EAV data modeling?