0

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

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

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

attribute_value:
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.

2
  • Strongly recommend you use JSONB instead. Feb 3, 2019 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, 2019 at 15:51

1 Answer 1

0

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
WHERE 
   a.label = 'marks12'
 AND 
   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

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.