THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

A Question of Values

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.

Published Monday, November 27, 2006 7:35 PM by Paul Nielsen

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Gaurav Bindlish said:

I had tried this approach last year for design on an application. The main problem I was facing was that the query response times were very slow. One of the design alternatives I tried was to have a column for each data type - int, char, GUID, time etc. This solved the problem you mentioned but the query times were still unaccptable.

January 31, 2007 12:59 PM

Leave a Comment


About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog



news item test
Privacy Statement