This can be useful whenever you need additional models w/ attributes that are not known in advance. For example you may want to allow your users to create a mask where they can enter data based on their own structure. e.g. google forms or strapi https://strapi.io/demo
I had to resort to Wikipedia before this even made sense[1].
Apparently it's a technique for efficiently storing small subsets of potentially enormous/variable/unknown attributes on objects.
Instead of a SQL table with 500 columns for all potential attributes (most of them NULL for most rows), you have a SQL table with three columns: the object in question, the attribute in question, and the attribute value. The first two columns are pointers to two other tables: a table of objects (PKs and other basic data), and a table of all the allowable attributes.
I can see how it's useful, in certain circumstances.
Come to think of it, several years ago I had to migrate a website from Wordpress (I think it was Wordpress) to Django. The Wordpress database schema was nothing but EAV tables: Every damn thing had all of its attributes listed in a separate table, and object retrieval traversed several tables each time.
I suppose this was necessary or at least useful for a general-use web framework like Wordpress, where no one knows what kinds of attributes various users will want to create, and therefore table _columns_ are stored as table _data_, and no one ever has to do any migration.
But it was dog-slow. The switch to Django halved the number of tables in the site, and the whole thing went about three times as fast (plenty of other reasons for that, though).
Granted, now I have to do Django migrations rather than Wordpress web-console manipulation, but as someone said in a different post, Django migrations aren't that hard these days.
Or at least necessary to evolve a relational schema from a simple blogging engine to a CMS that can do the things it can now (including the plugins). Advance Custom Fields, WooCommerce, LearnDash and the like are examples.
If it can't be shoehorned into the post table, you've got postmeta as a KV store for each post.
The code base looks like it was built the same way. I noted one developer saying "this is crazy... Its like one bootstrap file that bootstraps the next bootstrap file".
Using profiling tools, you're looking at tens to maybe over a hundred queries. It's a nightmare.
That's not at all true. Obviously the pattern can be misused but your own link mentions that it may be appropriate when the attributes need to be flexible or dynamic.
This is very useful if you want to ship new features without constantly changing DB tables by adding new fields to models.
Example scenario:
- Year 0: ship v1.0 of software with EAV table in place
- Year 1: a few months later you need to store some extra attributes for a feature, so you change the code to look for these attributes if they are available, and ship updates v1.1, v1.2, v1.2.3-experimentA, etc. none of which require DB migrations.
Like @lsaferite says, this is not super efficient as it requires an extra DB lookup when retrieving each object, but hey... features! (and less stressful software updates, since migrations will never break the system)
Using an EAV table also allows you to ship special features only for specific clients (e.g. special integrations that apply to only certain users or custom builds).
- Year 2: When shipping v2.0 you can convert all the attributes from successful feature experiments into "real" model attributes (field) and ship the v2.0 data model, possibly still keeping the EAV table around for v2.1, v2.2, etc.
I think it's a nice pattern since it puts all the "DB migration risk" into a few big chunks (less upgrade paths to test), but these days Django migrations have gotten so civilized that there is less need for this...
I’ve used the EAV pattern to allow users to add “custom fields” to an object.
The application has Person objects, who work for a Department. Lets say one department wants to record their employees’ shirt size, while another department wants to record their employees’ favorite color. Then, next week, they decide to also track their favorite brand of beer.
EAV allows department administrators to set this up on-the-fly without any development or database migrations.
Another way to tackle the same problem (used by Drupal, for example) is to actually modify the database schema at runtime, creating/modifying tables whenever your users want to change how the application behaves.
Yep, it's essentially the same thing. I guess EAV approach was the old way from before JSONField days.
In both cases the cost of this flexibility and migration-less data model extensibility is extra code paths and guards to check if these "optional" attributes are present or not. Spaghetti code anyone?
17 comments
[ 4.2 ms ] story [ 47.0 ms ] threadApparently it's a technique for efficiently storing small subsets of potentially enormous/variable/unknown attributes on objects.
Instead of a SQL table with 500 columns for all potential attributes (most of them NULL for most rows), you have a SQL table with three columns: the object in question, the attribute in question, and the attribute value. The first two columns are pointers to two other tables: a table of objects (PKs and other basic data), and a table of all the allowable attributes.
I can see how it's useful, in certain circumstances.
[1]: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...
I suppose this was necessary or at least useful for a general-use web framework like Wordpress, where no one knows what kinds of attributes various users will want to create, and therefore table _columns_ are stored as table _data_, and no one ever has to do any migration.
But it was dog-slow. The switch to Django halved the number of tables in the site, and the whole thing went about three times as fast (plenty of other reasons for that, though).
Granted, now I have to do Django migrations rather than Wordpress web-console manipulation, but as someone said in a different post, Django migrations aren't that hard these days.
If it can't be shoehorned into the post table, you've got postmeta as a KV store for each post.
The code base looks like it was built the same way. I noted one developer saying "this is crazy... Its like one bootstrap file that bootstraps the next bootstrap file".
Using profiling tools, you're looking at tens to maybe over a hundred queries. It's a nightmare.
Example scenario:
- Year 0: ship v1.0 of software with EAV table in place
- Year 1: a few months later you need to store some extra attributes for a feature, so you change the code to look for these attributes if they are available, and ship updates v1.1, v1.2, v1.2.3-experimentA, etc. none of which require DB migrations.
Like @lsaferite says, this is not super efficient as it requires an extra DB lookup when retrieving each object, but hey... features! (and less stressful software updates, since migrations will never break the system)
Using an EAV table also allows you to ship special features only for specific clients (e.g. special integrations that apply to only certain users or custom builds).
- Year 2: When shipping v2.0 you can convert all the attributes from successful feature experiments into "real" model attributes (field) and ship the v2.0 data model, possibly still keeping the EAV table around for v2.1, v2.2, etc.
I think it's a nice pattern since it puts all the "DB migration risk" into a few big chunks (less upgrade paths to test), but these days Django migrations have gotten so civilized that there is less need for this...
The application has Person objects, who work for a Department. Lets say one department wants to record their employees’ shirt size, while another department wants to record their employees’ favorite color. Then, next week, they decide to also track their favorite brand of beer.
EAV allows department administrators to set this up on-the-fly without any development or database migrations.
Another way to tackle the same problem (used by Drupal, for example) is to actually modify the database schema at runtime, creating/modifying tables whenever your users want to change how the application behaves.
In both cases the cost of this flexibility and migration-less data model extensibility is extra code paths and guards to check if these "optional" attributes are present or not. Spaghetti code anyone?
[1] https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/f...
https://en.m.wikipedia.org/wiki/Inner-platform_effect