Entity-attribute-value model using codeigniter / php

Question

SO I am trying to create a way to structure my database to be able customize forms.

I looked into EAV pattern and here is my db structure:

Table form - form_id - form_name - form_added_on - form_modified_at

Table: form_fields - field_id - form_id - field_type (TEXT, RADIO etc..) - field_default_value - field_required

Table: form_data - data_id - field_id - form_id - field_value

so now I can store any custom form into the database and if I want to get the values for an individual form I can simply join it by "form_id" ..

the problem:

I want to be able to search through all the forms for a specific field value.

How can I do that with EAV model?

Also, I thought about just storing the custom data as a serialized (JSON) object but then I am not sure how can I query that data.

Please note that I am using Codeigniter with MYSQL. So if conversation can use Codeigniter libraries if needed.

Answer 1

Disclaimer: I don't know PHP or CodeIgniter, but I'd willing to assert that neither has any sort of built in support for EAV. That noted, I know a lot about EAV so I'll answer in that light.

When you write about searching for a specific value, I'm assuming you mean in a particular field as well. So with that stated, put the data in a XML CLOB off of the form table (e.g. data) and use MySQL's XML functions to search against it. Seriously. Let's say the XML looks like:

<data>
    <field id="[field_id]">value</field>
</data>

Search it as as such:

SELECT f.form_id
FROM
  form f
WHERE 
  f.form_id = ?
  AND ExtractValue(data, '//field[@id="[field_id]"]') = ?

Why? The issue is searching multiple criteria against an EAV model is challenging. Consider this example:

SELECT f.form_id
FROM
  form f
    INNER JOIN form_fields f1 ON f1.form_id = f.form_id
    INNER JOIN form_fields f2 ON f2.form_id = f.form_id
WHERE 
  f.form_id = ?
  AND 
  (f1.field_id = ? AND f1.field_value = ?)
  AND 
  (f2.field_id = ? AND f2.field_value = ?)

This all seems well and good, now change the AND to OR and all hell breaks loose.

SELECT f.form_id
FROM
  form f
    INNER JOIN form_fields f1 ON f1.form_id = f.form_id
    INNER JOIN form_fields f2 ON f2.form_id = f.form_id
WHERE 
  f.form_id = ?
  OR 
  (f1.field_id = ? AND f1.field_value = ?)
  OR 
  (f2.field_id = ? AND f2.field_value = ?)

Do you see the issue? The INNER JOIN in the FROM clause means records no matter what, data is returned no matter the WHERE clause. So instead of JOIN in the FROM clause + WHERE clause, EAV demands an EXISTS in the WHERE:

SELECT f.form_id
FROM
  form f
WHERE 
  f.form_id = ?
  AND (
    EXISTS (
      SELECT f1.form_id FROM form_fields f1 
      WHERE f1.field_id = ? AND f1.field_value = ? AND f1.form_id = f.form_id
    )
    -- note OR search 
    EXISTS (
      SELECT f2.form_id FROM form_fields f2
      WHERE f2.field_id = ? AND f2.field_value = ? AND f2.form_id = f.form_id
    )
  )

Kinda ugly huh? That and MySQL subquery performance is not good. Add in the desire to search on specific data types, such as dates and integers, and you're either casting or working with multiple columns in the form_field table (e.g. field_date_value, field_int_value, etc...).

So the XML CLOB means only table to consider when querying and also the possibility of multivalued attributes as well (multiselects or checkboxes come to mind).