undefined | Better HN

0 pointscollyw5y ago0 comments

EAV is considered an antipattern. While wouldn't rule out using it, it probably means it should be avoided if there is another solution.

0 comments

3 comments · 1 top-level

SahAssar5y ago· 2 in thread

I've been working on a project using schema.org objects (100's of types of objects) in postgres in a graph/tree like structure. While structured the objects are not consistent enough for me to want separate schemas for them. Using JSONb in postgres allows me to index separate fields within the JSON while still having all the objects in a single graph.

If I couldn't use JSONb with indexes (and triggers for custom foreign key checks) it'd be much harder to do this in in a relational database (and the data is relational, it just isn't consistent across all types).

Perhaps this would be better in a Triplestore but there are a lot of features of postgres that are not available in them.

formerly_proven5y ago

I used to believe in these database antipatterns (e.g. avoid EAV, avoid attribute tables, avoid denormalization, avoid overnormalization, avoid inheritance, avoid wide tables, only use synthetic keys, only use natural keys, never use autoincrement, always use autoincrement), but these days I'm almost certain all of them are wrong, because they are independent of business domain needs. Business domains have antipatterns for their data, data in general decidedly does not. For example, storing physical addresses is subject to a number of antipatterns, including stuff like "natural keys are not a good idea for this".

GP cited EAV as an antipattern, yet it is one of the most reasonable approaches to user-defined fields (especially when suitably augmented), which are frequently a business requirement.

andrewingram5y ago

Not even just user-defined fields, any time where the fields need to be defined as data values rather than part of your concrete data model. Product attributes on a big multi-category e-commerce site being another example. You could just stick them in JSON, but it's still essentially EAV, just with a different storage mechanism.

Another advantage of EAV as a pattern, is it makes it straightforward to add metadata to values, like "where did this value come from?". For systems with audit trails, just having keys and values isn't enough.

EAV wouldn't be the first tool I'd jump to -- I've definitely seen it go wrong -- but it feels like it's going too far to call it an anti-pattern.

j / k navigate · click thread line to collapse