EAV systems are meant to model sparse databases--systems where the number of possible attributes for any entity overwhelms the number of attribute values. A typical example is a medical records system. For general introductions to EAV systems see
http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm
http://en.wikipedia.org/wiki/Entity-Attribute-Value_model
http://www.devshed.com/c/a/MySQL/Database-Design-Using-KeyValue-Tables
For more discussion on EAV queries in general, see:
http://www.pubmedcentral.nih.gov/articlerender.fcgi?tool=pubmed&pubmedid;=9824799
EAV queries are notorious for difficulty and inefficiency, but here is a delightfully clear EAV example posted in the MySQL Newbie forum by a user with the monicker "laptop alias". You track orders and their options in this single EAV table:
CREATE TABLE order_info(
order_id INT NOT NULL,
product_id INT NOT NULL,
option_attribute VARCHAR(20),
option_value VARCHAR(20),
PRIMARY KEY(order_id,product_id,option_attribute,option_value)
);
INSERT INTO order_info VALUES
(1,10,'Colour','Red'),(1,10,'Size','Large'),(1,10,'Sleeve','Short'),
(2,10,'Colour','Blue'),(2,10,'Size','Small'),(2,10,'Sleeve','Long');
How would you report that data with options for each order on one line?
SELECT
order_id AS `Order`,
product_id AS Product,
MAX(CASE WHEN option_attribute = 'Colour' THEN option_value END) AS Colour,
MAX(CASE WHEN option_attribute = 'Size' THEN option_value END) AS Size,
MAX(CASE WHEN option_attribute = 'Sleeve' THEN option_value END) AS Sleeve
FROM order_info
GROUP BY order_id, product_id;
+-------+---------+--------+-------+--------+
| Order | Product | Colour | Size | Sleeve |
+-------+---------+--------+-------+--------+
| 1 | 10 | Red | Large | Short |
| 2 | 10 | Blue | Small | Long |
+-------+---------+--------+-------+--------+
The unmentioned trick is that to write this query, you need the results of ...
SELECT DISTINCT option_attribute FROM order_info;
+------------------+
| option_attribute |
+------------------+
| Colour |
| Size |
| Sleeve |
+------------------+
Here is another small example, this one using nested queries, that we ported to MySQL from SQL Server. The problem is to write a query for these two simple EAV tables ...
DROP TABLE IF EXISTS tracking,trackingdata;
CREATE TABLE tracking (
id int primary key auto_increment,
keyname char(16),
keyvalue char(32)
);
insert into tracking VALUES (1,'LOCATION','Bedroom');
insert into tracking VALUES (2,'LOCATION','Dining Room');
insert into tracking VALUES (3,'LOCATION','Bathroom');
insert into tracking VALUES (4,'LOCATION','courtyard');
insert into tracking VALUES (5,'EVENT', 'verbal aggression');
insert into tracking VALUES (6,'EVENT', 'peer');
insert into tracking VALUES (7,'EVENT', 'bad behavior');
insert into tracking VALUES (8,'EVENT', 'other');
CREATE TABLE trackingdata (
id int primary key auto_increment,
bdsid int,
keyname char(16),
keyvalue char(128)
);
insert into trackingdata VALUES (1, 1, 'LOCATION', 'Bedroom');
insert into trackingdata VALUES (2, 1, 'EVENT', 'other');
insert into trackingdata VALUES (3, 1, 'EVENT', 'bad behavior');
insert into trackingdata VALUES (4, 2, 'LOCATION', 'Bedroom');
insert into trackingdata VALUES (5, 2, 'EVENT', 'other');
insert into trackingdata VALUES (6, 2, 'EVENT', 'verbal aggression');
insert into trackingdata VALUES (7, 3, 'LOCATION', 'courtyard');
insert into trackingdata VALUES (8, 3, 'EVENT', 'other');
insert into trackingdata VALUES (9, 3, 'EVENT', 'peer');
... which lists all defined events at all defined locations with their counts:
Select
Locations.LocationValue As Location,
Events.EventValue As Event,
(
Select Count(*) From (
Select LocationData.LocationValue, EventData.EventValue
From (
Select TD1.bdsid, TD1.keyvalue As LocationValue
From TrackingData As TD1
Where TD1.keyname = 'LOCATION'
) As LocationData
Join (
Select TD2.bdsid, TD2.keyvalue As EventValue
From TrackingData As TD2
Where TD2.keyname = 'EVENT'
) As EventData On LocationData.bdsId = EventData.bdsId
) As CollatedEventData
Where CollatedEventData.LocationValue = Locations.LocationValue
And CollatedEventData.EventValue = Events.EventValue
) AS Count
From (
Select T1.keyvalue As LocationValue
From Tracking As T1
Where T1.keyname = 'LOCATION'
) As Locations,
(
Select T2.keyvalue As EventValue
From Tracking As T2
Where T2.keyname = 'EVENT'
) As Events
Order By Locations.LocationValue, Events.EventValue
If this doesn't discourage you, frequent MySQL forum contributor Rick James has assembled a list of relevant forum posts on EAV:
http://forums.mysql.com/read.php?10,355723 (Searching by using an index table, efficient queries?)
http://forums.mysql.com/read.php?125,354578,354578
http://forums.mysql.com/read.php?10,248799,249039
http://forums.mysql.com/read.php?10,359004 (GROUP_CONCAT question)
http://forums.mysql.com/read.php?10,358797 (joined query help needed)
http://forums.mysql.com/read.php?10,358687 (Update query)
http://forums.mysql.com/read.php?10,358153 (Sub-request with multiple result as condition)
http://forums.mysql.com/read.php?10,358143 (two tables, one table has value of 2nd table name)
http://forums.mysql.com/read.php?10,354528 (SQL long Join statement and performance)
http://forums.mysql.com/read.php?10,304620 (Query problem)
http://forums.mysql.com/read.php?10,293310 (Need select query for eav modeling)
http://forums.mysql.com/read.php?10,293211 (UPDATE TABLE with TRIM() => Duplicate key problems)
http://forums.mysql.com/read.php?20,292439 (SQL Syntax)
http://forums.mysql.com/read.php?10,290756 (combining multiple rows)
http://forums.mysql.com/read.php?10,289808 (Export Data from multiple tables and Later export it to CSV)
http://forums.mysql.com/read.php?10,289079 (Query between 2 related tables - duplicated records in the result set for every record in the 2nd table that it matches.)
http://forums.mysql.com/read.php?10,288978 (Need help with advanced Joins) |
|