Database EAV model, record listing as per search

Question

I am building a dynamic application. I have three tables : ( EAV model style)

1. Items ( ItemId, ItemName)
2. Fields (FieldId, FieldName)
3. Field Values ( ItemID, FieldId, Value)

Can you tell me how to write SINGLE query to get starting 20 records from ALL items where FieldId=4 is equal to TRUE.

Expected Result :

Columns =>  ItemID | Name  | Field1 | Field2 |  Field3  
Each Row=>  ItemId | ItemName| Value1 | Value2 | Value3

Important concerns :

1. Number of fields per item are not known
2. I need one to write ONE query.
3. Query will be running on 100K records, so performance is concern.
4. I am using MySQL 5.0, so need solution for MYSQL

Should I denormalize the tables if above query is not possible at all ? Any advice ?

Answer 1

The EAV design is denormalized. That is, it's a non-relational design. There is no rule of normalization that would lead you to use the EAV design.

SQL requires that you know the columns when you write the query, and also that every row of the result set has the same columns. With EAV, the only solution if you don't know how many fields per item is to fetch them back as rows, not columns.

SELECT i.ItemID, i.ItemName, f.FieldName, v.Value
FROM Items i
JOIN FieldsValues v4 ON (v4.ItemID, v4.FieldID, v4.Value) = (i.ItemID, 4, TRUE)
JOIN FieldsValues v ON i.ItemID = v.ItemID
JOIN Fields f ON v.FieldID = f.FieldID;

You have to process the rows in your application. For instance, with PHP:

<?php

$pdo = new PDO(...);
$sql = "...above query...";

$collection = array();

foreach ($pdo->query($sql) as $row) {
  $id = $row["ItemID"];
  if (!array_key_exists($id, $collection)) {
    $collection[$id] = new stdClass();
    $collection[$id]->Name = $row["ItemName"];
  }
  $collection[$id]->$row["FieldName"] = $row["Value"];
}

Now you have an array of objects, and each object corresponds to an item from the database. Each object has its own respective set of fields.