Entity-Attribute-Value database anti-pattern

EAV stores data in an unstructured way in the database. It is often used when it is not fully known what data would be stored in the database.

The issue with EAV is while you simply the storing of data, you greatly increase the complexity of handling the meta data. For example, you may require two database queries just to get the right data that you are looking for.

This method increases:

• network traffic

• complexity in the application severely

• time to generate reports severely

It is also frequently difficult to conceptually wrap your head around how to get several bits of data for a user/order/invoice/wallet etc..

From the hardware itself, it is also difficult to gather the data. Single items are not too bad, but when you need several, it is sub-optimal to grab data from several (possibly) dispersed pages on disk. Rather than simply get it in one sweep of the disk.

Indexes also need to duplicate the number of rows they support which means that their leaf/nodes hierarchies need to be duplicated.

To solve try to reduce the meta data complexity while still allowing for flexibility, there is the option of using JSON datatypes – with 5.7 – or text datatypes with <5.7 where you manage the JSON validity in the application.

Another option is to have EAV but having some enforced structure to it. For example, you can have the dataKey as a fixed Enum in the table or maybe a Enum for group and an Enum for key.

Lastly, now that we are aware of tools that allow for online schema changes, we can decide on the columns needed for now with the knowledge that it won’t be too difficult to add more in the future.