| 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 2025 |