Trying to convince a coworker to NOT use entity-attribute-value (EAV)

Question

They really like the flexibility of it, being able to make a database update and then allow your users to collect a new data point. I've sent them

https://tapoueh.org/blog/2018/03/database-modelization-anti-patterns/#entity-attribute-values And https://www.cybertec-postgresql.com/en/entity-attribute-value-eav-design-in-postgresql-dont-do-it/

I'm trying to emphasize that I like reporting to remain easy and efficient, and being able to do constraints and basically use an RDS etc...

The attributes are not many and I don't see them needing more daily, maybe adding one or two quarterly. I've trying to stave off the 'pre-optimization' of basically if we do EAV the problem is solved.

One annoyance is having to update the client code if a new column was added, I've been pondering offering a dynamic postgres function that could just insert a value into a user defined column as kindof a compromise.

Any other good articles or advice are welcome

Answer 1

You have to rebuild all database technology, like concurrency, constraints, data integrity, etc. in your application. Using EAV you don't have data types anymore, so 1 and one will be both valid for a number. Just like banana.

I have seen successful EAV implementations, but only for reporting databases where there was no concurrency and all constraints and data integrity was handled in the main production (OLTP) database.

One annoyance is having to update the client code if a new column was added

You have to update the client code anyway. There is no code in the world that knows what to do when a new attribute suddenly pops up in the database.