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.