David.Poole (1/31/2013)
@Jeff What sort of size, scale and traffic are you putting through your EAV models?
Some of the tables got quite large (several million rows). The post just a couple up from here is a reasonable example of what one app used for contact info. We had one for address information, as well. Proper indexing on the "entity" and a bit of dynamic SQL as a dynamic crosstab worked well in most of the instances.
It's definitely not the path I'd have liked to travel down but we made it work and, especailly for what it did, it worked quite fast (apologies... it was several years ago and I don't have the metrics available). A side advantage was it did greatly simplify certain types of searches.
I have to admit that one of my favorite types of EAV is for data collection by automation. Something with a device number, a date, and some value. Makes life super easy for creating reports by just about any time frame you can imagine. In those areas, many millions of rows and hundreds of read and thousands of writes per minute. On the IVR system I wrote, I could do reporting by hour of the day across 9 different catagories for every day of a month including category grand and cumulative totals on 12 million rows of data in about 5 seconds. It would have been a huge pain without the use of an EAV.
Again, I hate to call these things "models" because that implies a much broader scope than what we used. We had some EAV "tables".
--Jeff Moden
Change is inevitable... Change for the better is not.