Entity-Attribute-Value

In the DB schema of a previous project (which was a 10 year old legacy system), there was a table with a set of attributes listed as rows instead of columns as shown below.

When asked why, it was told that there was a client request to add additional parameters to the product entity, but adding a new column was not feasible since the table contained millions of records. Instead they opted for this solution as a workaround. Two new tables were added and the "Product_Config_Param_Def" contained the new parameter definitions and "Product_Config_Param_Value" contained the corresponding values. This was flexible and allowed adding many more parameters without having to add columns. And the plus point is when there are products that don't have the entire set of parameters, we don't need to populate DB with nulls.

This is what is known as "Entity-Attribute-Value" Modeling (EAV) and it's a great way for us to get closer to the benefits offered by No SQL DBS with our relational DBs. No SQL DB of course offer us unlimited flexibility in adding dynamic attributes and is the way to go if our entities have large amount of varying attributes (In other words, when entities have different sets of attributes) and the relationships between entities is rather limited (Think FB accounts).

With EAV, data is modeled as attribute-value pairs.

EAV should be used with caution. Many identify it as an anti-pattern. It can be such a performance hit. If I may copy the example mentioned in http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

With no EAV:

select ename from emp where job=’CLERK’ and sal < 2000;

With EAV:

select ev1.name
from emp_values ev1, emp_values ev2 emp_values ev3
where ev1.code = ‘NAME’
and ev1.empno = ev2.empno
and ev2.code = ‘JOB’
and ev2.value = ‘CLERK’
and ev1.empno = ev3.empno
and ev3.code = ‘SAL’
and TO_NUMBER(ev3.value) < 2000;