I have implemented an EAV db pattern as part of a PoC in Postgres. This is the following ER illustration:

id, primary key 
firstName, varchar
lastName, varchar
tenantId, int, b-tree indexed

id, primary key
label, varchar
type, varchar, (enumerated as string from java enum , NUMERIC, TEXT, DATE)

id, primary key 
attribute_id, fk reference attribute primary key
entity_id, fk reference entity primary key
value, varchar

Now the problem I am facing is comparing values in the joined tables. For example:

How do I check whether an attribute with label = 'marks12' has value < 100

Comparing the value::int does not solve the problem as there are other attribute such as gender that cannot be cast into int value. How do I perform such value-centric conditions in the above EAV design model?

PS: I am open to other database designs which allow to map/store dynamic attributes in an RDBMS.

  • Strongly recommend you use JSONB instead. – Colin 't Hart Feb 3 '19 at 15:45
  • @Colin'tHart I have already done that. I am just trying to benchmark the performance eav design vs jsonb design – Dota2 Feb 3 '19 at 15:51

easiest is to ignore anything that does not look like an integer.

select e.* , av.value
FROM attribute_value AS av 
JOIN attribute AS a ON av.attribute_id = a.id
JOIN entity AS e ON av.entity_id = e.id
   a.label = 'marks12'
   CASE WHEN av.value ~ '[+-]?[0-9]{1,20}' THEN av.value::bigint END < 100;

this is much harder when value might be a date,

Your Answer

