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.