Every couple of months, I hear about data modelers working with the value-pairs pattern, also called the EAV pattern, the generic pattern, or informally called the diamond pattern. In SQL Server 2000 Bible, I called it the relational-dynamic pattern. Basically, it’s a simple design with only four tables: class/type, attribute, object/item, value. The value table stores every value for every attribute for every item – one long list.
For Nordic (New object/relational design), I used the value-pairs pattern for one of the iterations as a test and rejected it, adopting the concrete class pattern instead.
At first blush, the value-pairs pattern is attractive, novel, and appealing. It offers unlimited logical design alterations without any physical schema changes – the ultimate flexible extensible design. But there are problems.
My first issue is with data typing. The data type is the most basic data constraint. The basic value-pairs pattern stores every value in a single nvarchar column and ignores data typing. A popular option is to create a value table for each data type. While this adds data typing, is certainly complicates the code.
Which brings us to the second issue with the value-pairs pattern: it’s difficult to query. I’ve seen two solutions. The most common method is hard coding .net code to extract and normalize the data. Another option is create a table-valued UDF for each class/type to extract the data and return a normalized data-set. This has the advantage of being able to be used in normal SQL queries, but performance and inserts/updates remain difficult. Either solution defeats the dynamic goal of the pattern.
Can the value-pairs pattern be an efficient, practical solution? I doubt it. The real test would require some serious code-generation so that the add property procedure would generate the procedures, views, and functions needed for inserting, updating, and selecting. Could be fun though.