Pros: querying complex data hierarchies was easy, and was able to skip the pain typically associated with managing a SQL schema.
> EAV is often an anti-pattern when a schema could be defined
Super interesting, I wasn't aware that EAV is an anti-pattern in that case. Is it an efficiency thing?
For clarity, my design wasn't schemaless, values (can) have defined datatypes and relationships are first-class. I meant that I found adding to or modifying the schema was less cumbersome and error prone than traditional SQL schema additions or changes. I feel like SQL schema management is more suited to server-based dbs where you have tight control over the db lifecycle, which you don't when it lives on a bunch of mobile devices.
Totally agree with the ease of sync and conflict resolution, another strong pro.
Love to hear more about your approach! Also feel free to reach out (email in bio) if you'd like to compare notes some time.
It was built on a single table that held the entity-attribute-value tuple along with some additional metadata like type information, whether or not the attribute was a pointer to another entity, and the cardinality of that relationship (one or many).
Relationships were walked via self joins and the eav columns were all indexed.
The difficulty with having all attributes be EAV becomes apparent when having to do multiple joins to fetch a single record type (what would be a “table” traditionally). Although this is manageable, the bigger difficulty I’ve found is synchronizing deletions of records, especially if deletions/insertions are done in bulk. Rather than just 1 transaction you have to do multiple delete/insert queries to also delete/insert the attributes and the values and they should be done in a way that doesn’t break key constraints.