3 ms·

SELECT count(likes_a) FROM facts as likes_a, facts as likes_b WHERE likes_a.attribute = ‘like’ AND likes_b.attribute = ‘like’ AND likes_a.value = likes_b.value

by fogzen 3y ago

SELECT count(likes_a) FROM facts as likes_a, facts as likes_b WHERE likes_a.attribute = ‘like’ AND likes_b.attribute = ‘like’ AND likes_a.value = likes_b.value AND likes_a.entity_id != likes_b.entity_id AND likes_b.entity_id = 123

hot_gril 3y ago
Ah, I also should've looked up EAV since there's a lot on this. I don't see what you gain from this other than not having to define new tables/cols for new use cases, and the downside is any DB structure has to be enforced entirely in code. I've been down routes like this before (not EAV but other sorta polymorphic DBs), and it's never ended well. Became unsafe to touch the code at some point, especially in a team setting. On the more technical side, I don't know if Postgres would handle this efficiently. The `attribute` col has the classic low-cardinality problem for indexes. The one big table you use would have many very different access patterns. There are DBMSes more designed for this use case. I'm also not seeing very much EAV adoption mentioned on Wikipedia. I can see this working for some specialized use cases, though. Has this worked well for you in the past?
- fogzen 3y ago
  I personally think it’s safer to enforce data constraints in application code, because “bad” data won’t break it. Putting the constraints in the database means your application is coupled to whatever database you use. Worse, because databases only offer a small set of constraints, it’s often the case that constraints are split between the database and application code. You have “validations” in both the application code and database definitions, and data must be checked/transformed anyway whenever it is marshaled to and from the database. Constraints in the database are effectively implicit, living in a set of migrations or often not part of any source control. EAV indexing is much easier, because EAV is naturally represented as a tree. The whole database is the index. You only have four universal indexes that cover every query: EAV, AEV, AVE, and VAE. The mutual likes example above is covered by these indexes. And you never had to worry about it! See Datomic for real world example of an EAV architecture.

by fogzen 3y ago

hot_gril 3y ago

Ah, I also should've looked up EAV since there's a lot on this. I don't see what you gain from this other than not having to define new tables/cols for new use cases, and the downside is any DB structure has to be enforced entirely in code. I've been down routes like this before (not EAV but other sorta polymorphic DBs), and it's never ended well. Became unsafe to touch the code at some point, especially in a team setting. On the more technical side, I don't know if Postgres would handle this efficiently. The `attribute` col has the classic low-cardinality problem for indexes. The one big table you use would have many very different access patterns. There are DBMSes more designed for this use case. I'm also not seeing very much EAV adoption mentioned on Wikipedia. I can see this working for some specialized use cases, though. Has this worked well for you in the past?

fogzen 3y ago
I personally think it’s safer to enforce data constraints in application code, because “bad” data won’t break it. Putting the constraints in the database means your application is coupled to whatever database you use. Worse, because databases only offer a small set of constraints, it’s often the case that constraints are split between the database and application code. You have “validations” in both the application code and database definitions, and data must be checked/transformed anyway whenever it is marshaled to and from the database. Constraints in the database are effectively implicit, living in a set of migrations or often not part of any source control. EAV indexing is much easier, because EAV is naturally represented as a tree. The whole database is the index. You only have four universal indexes that cover every query: EAV, AEV, AVE, and VAE. The mutual likes example above is covered by these indexes. And you never had to worry about it! See Datomic for real world example of an EAV architecture.