As an aside… I would truly love to explore a collection of interesting ways to use SQLite. It’s such an impressive piece of technology that I’d like to use more often. Please share if you have something similar!
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.
The closest thing is this:
https://munin.uit.no/bitstream/handle/10037/22344/thesis.pdf
There it split each record in a stream of CRDTs values. I found (quickly!) that it could cause serious violations of business logics if done as-is. Now, I trying to threat the record as whole. Still could have issues for multi-record/table logical integrity, so I have tough in build a "transaction markers" so your stream of changes are:
Start
ADD: T1.Row1...
ADD: T2.Row1...
End
P.D: If interested and know Rust we can talk!
The project is both interesting and amusing, thanks!