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
value::int does not solve the problem as there are other attribute such as
gender that cannot be cast into
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.