EAV designs and queries

from the Artful MySQL Tips List


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

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 years ago in the MySQL Newbie forum. 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'), (2,'LOCATION','Dining Room'),
(3,'LOCATION','Bathroom'), (4,'LOCATION','courtyard'),
(5,'EVENT', 'verbal aggression'), (6,'EVENT', 'peer'),
(7,'EVENT', 'bad behavior'), (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'), (2, 1, 'EVENT', 'other'),
(3, 1, 'EVENT', 'bad behavior'), (4, 2, 'LOCATION', 'Bedroom'),
(5, 2, 'EVENT', 'other'), (6, 2, 'EVENT', 'verbal aggression'),
(7, 3, 'LOCATION', 'courtyard'), (8, 3, 'EVENT', 'other'),
(9, 3, 'EVENT', 'peer');
... to list all defined events and their counts at all defined locations ...
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
Cross Join (
  Select T2.keyvalue As EventValue
  From Tracking As T2
  Where T2.keyname = 'EVENT'
) As Events
Order By Locations.LocationValue, Events.EventValue ;
+-------------+-------------------+-------+
| Location    | Event             | Count |
+-------------+-------------------+-------+
| Bathroom    | bad behavior      |     0 |
| Bathroom    | other             |     0 |
| Bathroom    | peer              |     0 |
| Bathroom    | verbal aggression |     0 |
| Bedroom     | bad behavior      |     1 |
| Bedroom     | other             |     2 |
| Bedroom     | peer              |     0 |
| Bedroom     | verbal aggression |     1 |
| courtyard   | bad behavior      |     0 |
| courtyard   | other             |     1 |
| courtyard   | peer              |     1 |
| courtyard   | verbal aggression |     0 |
| Dining Room | bad behavior      |     0 |
| Dining Room | other             |     0 |
| Dining Room | peer              |     0 |
| Dining Room | verbal aggression |     0 |
+-------------+-------------------+-------+
If this doesn't discourage you, former MySQL forum contributor Rick James 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)

Last updated 21 Feb 2020


Return to the Artful MySQL Tips page