Featured Post

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:


Comments

Related Posts

Scrum Roles (Scrum Master, Product owner, Development Team) and collaboration between each other

Team Roles/Group Roles (Belbin) within the project and Four classes of culture

The bug in the name of the C++ language.

Working with Git Repositories Using GitHub Desktop

Difference in Verification and Validation of requirement? : Requirement Traceability Matrix?