Greg Low has started a series on entity-attribute-value tables. The first post covers what they are:
If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) tables (or non-tables as some of my friends would call them).
Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.
The second post covers pros and cons:
In an earlier post , I discussed the design of EAV (Entity Attribute Value) tables, and looked at why they get used. I’d like to spend a few moments now looking at the pros and cons of these designs.
Greg is very much against EAV, and I agree with this. I do like Greg’s alternative of using something like JSON, with the proviso that the database simply become a whole-record storage and retrieval engine rather than trying to strip out and splice in new JSON via T-SQL. Otherwise, spend the time on proper data modeling and take advantage of what the platform can do for you.
EAV tables to avoid adding sparse columns e.g. phone1 phone2 phone3 to normal(ized) tables is quite alright with me. Jamming JSON in relational tables is not. There is a 100% chance that will be abused in the long term.