THE SQL Server Blog Spot on the Web

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

Paul Nielsen

www.SQLServerBible.com

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

Comments

 

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
New Comments to this post are disabled

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

Syndication

News

news item test
Privacy Statement