Database Reference
In-Depth Information
Entity attribute values
The final substantive pattern from relational modeling is the entity-attribute-value (EAV)
schema, where you would create a meta-model for product data. In this approach, you main-
tainatablewiththreecolumns(e.g.,
entity_id
,
attribute_id
,and
value
),andthesetriples
describe each product.
Consider the description of an audio recording. You may have a series of rows representing
the following relationships:
Entity
Attribute Value
sku_00e8da9b type
Audio Album
sku_00e8da9b title
A Love Supreme
sku_00e8da9b …
…
sku_00e8da9b artist
John Coltrane
sku_00e8da9b genre
Jazz
sku_00e8da9b genre
General
…
…
…
This schema is completely flexible:
▪ Any entity can have any set of any attributes.
▪ New product categories do not require
any
changes to the data model in the database.
There are, however, some significant problems with the EAV schema. One major issue is that
all nontrivial queries require large numbers of
JOIN
operations. Consider retrieving the title,
artist, and two genres for each item in the table:
SELECT
SELECT
entity
,
t0
.
value
AAS
title
,
t1
.
value
AAS
artist
,
t2
.
value
AAS
genre0
,
t3
.
value
aas
genre1
FROM
FROM
eav
AAS
t0
LEFT
LEFT JOIN
JOIN
eav
AAS
t1
OON
t0
.
entity
=
t1
.
entity
