Microsoft® SQL Server® 2012 Bible

163

Chapter 7: Relational Database Design and Creating the Physical Database Schema

7

Entity-Value Pairs Pattern

Entity -value pairs pattern, also known as the entity-attribute-value (EAV) pattern, sometimes called the

generic pattern or property bag/property table pattern, illustrated in Figure 7-14, is another database

design pattern often used by data modelers. This is not a popular design pattern, but in some cases

it lends itself well to the problem you are attempting to solve.

Value

Class

Category

Attribute

Property

Object

Item

FIGURE 7-14

The entity-values pairs pattern is a simple design with only four tables: class/type, attribute/column,

object/item, and value. The value table stores every value for every attribute for every item — one

long list.

This design can be popular when applications require dynamic attributes. Sometimes it’s used as an

Object Oriented (OO) DBMS physical design within an RDBMS product. It’s also gaining popularity

with cloud databases.

At fi rst blush, the entity-value pairs pattern is attractive, novel, and appealing. It offers unlimited logi-

cal design alterations without any physical schema changes — the ultimate fl exible extensible design.

But there are problems — many problems:

■ (^) The entity-value pairs pattern lacks data integrity — specifi cally, data typing. The data type
is the most basic data constraint. The basic entity-value pairs pattern stores every value in
a single nvarchar or sql_variant column and ignores data typing. One option not rec-
ommended is to create a value table for each data type. Although this adds data typing, it
certainly complicates the code.
■ (^) It’s diffi cult to query the entity-value pairs pattern, and there are two solutions. The most
common method is hard-coding .NET code to extract and normalize the data. Another
option is to code-gen a table-valued UDF or crosstab view for each class/type to extract
the data and return a normalized data set. This has the advantage of being usable in normal
SQL queries, but performance and inserts/updates remain diffi cult. Either solution defeats
the dynamic goal of the pattern.
■ (^) Perhaps the greatest complaint against the entity-value pairs pattern is that it’s nearly
impossible to enforce referential integrity.
..
c07.indd 163c07.indd 163 7/30/2012 4:18:16 PM7/30/2012 4:18:16 PM
http://www.it-ebooks.info