PHP & MySQL Tip #3 – EAV Modeling w/ PHP & MySQL

PHP & MySQL Tip #3

Update Nov 11th, 2013: Since this tends to be the first and last page most people visit on my blog I’ve updated the code examples and provided them on github at:
https://github.com/dschreck/php-eav-example
or for download at: php-eav-example-master

I want to share with some of you an easy way to set up your database design in a very flexible and reliable EAV like model.

EAV stands for Entity Attribute Value, which is a common design for complex database structures that require many different Entities using many different attributes with, again, many different values. It’s very common to find this set up in medical offices or records.

Let’s go ahead and assume a situation, and we’ll work through it.

Let’s say you’re accepting a feed of some sort, and you need to save the items being fed to you into a database. The problem here, is that the content length of these items, attributes, and values varies.

So let’s consider the following:

Item 'Node1' ->
	Attribute 'Main' ->
		Value 1
		Value 2
		Value 3
		Value 4
Item 'Node2' ->
	Attribute 'Main' ->
		Value 1
		Value 2
		Value 3
		Value 4
	Attribute 'Other' ->
		Value 1
		Value 2

Item 'Node3' ->
	Attribute 'Main' ->
		Value 1
		Value 2
		Value 3
		Value 4
		Value 5
		Value 6
		Value 7
		Value 8

Now this is a very cheap and generic example of a data structure. But we’ll work with it for now.

So let’s go ahead and draw some conclusions.

The Item list will come in with a name, and have Attributes. These attributes will have values. But the number of attributes and the number of values varies.

So with that in mind, we shall come up with some SQL to create some times. For this example we’re going to need three tables: items, item_attributes, and attribute_values.

So here we go:

-- first our items table:
CREATE TABLE `items` (
`id` int(11) NOT NULL auto_increment,
`item_name` varchar(50) default NULL,
PRIMARY KEY  (`id`)
);

-- now our item attributes
CREATE TABLE  `item_attributes` (
`id` int(11) NOT NULL auto_increment,
`item_id` int(11) NOT NULL default '0',
`attribute_name` varchar(50) default NULL,
PRIMARY KEY  (`id`),
KEY `item_id_attribute_name` (`item_id`,`attribute_name`)
);

-- now finally our attribute values
CREATE TABLE  `attribute_values` (
`attribute_id` int(11) NOT NULL default '0',
`attribute_value` varchar(100) default NULL,
UNIQUE KEY `attribute_id` (`attribute_id`,`attribute_value`)
);

Now that we have our layout, let’s take a look at how this is going to work:

Let’s assume the following PHP array is a reprentation of our data…

<?php

// start up our array
$data = array();
//
// Now, let's just load it with some test data
$data['item_1'] = array();
$data['item_1']['attribute_1'] = array();
$data['item_1']['attribute_1'][] = 'value1';
$data['item_1']['attribute_1'][] = 'value2';
$data['item_1']['attribute_1'][] = 'value3';
$data['item_1']['attribute_1'][] = 'value4';
// that's good for now.

/**
* Now let's insert this int our new schema
*
* Please note, for example sake, I will not be double checking queries
* but you SHOULD check each query for an error.
**/

foreach($data as $item_name => $attributes)
{
	$sql = "INSERT INTO items (id, item_name) VALUES (NULL, '{$item_name}');";
	mysql_query($sql);
	$item_id = mysql_insert_id();
	// now let's loop through our attributes
	foreach($attributes as $attribute => $values)
	{
		// this is now our insert into the attributes...
		$sql = "INSERT INTO item_attributes (id, item_id, attribute_name) VALUES (NULL, {$item_id}, '{$attribute}');";
		mysql_query($sql);
		$attribute_id = mysql_insert_id();
		// now let's loop through the attribute values
		foreach($values as $value)
		{
			$sql = "INSERT INTO attribute_values (attribute_id, attribute_value) VALUES ({$attribute_id}, '{$value}');";
			mysql_query($sql);
		}
	}
}
?>

And there you have it – that’s now how we can get our data into our database in a flexiable manner, without having to rely
on an ‘excel’ like database.

To get it out, we’ll simply use some joins…

$sql =
	"SELECT
			items.item_name,
			ia.attribute_name,
			av.attribute_value
	 FROM
			attribute_values AS av
		JOIN item_attributes AS ia
			ON (ia.id = av.attribute_id)
		JOIN items AS items
			ON (items.id = ia.item_id);
	";

You could also use a concat_ws to make a comma seperated list, but now that you have it in your database, you can do anything you want with it :)

13 thoughts on “PHP & MySQL Tip #3 – EAV Modeling w/ PHP & MySQL

  1. edesign

    Your SELECT statement only returns one attribute? Can you elaborate on the SELECT statement to bring back several attributes at once? I think in the EAV model this is called Pivoting, I was wondering if there was a way to do from MySQL directly?

    Reply
  2. dschreck Post author

    edesign:
    Pivoting is generally used when combining any type of row-modeled data, you’ll usually combine that into a column like set up, for using ‘off the shelf’ reporting software.

    I’m not exactly sure what you mean when you say it only brings back one attribute?

    Using the direct copy and paste of the tables above, i quickly typed out these test data inserts:


    insert into items values (1, 'item 1'), (2, 'item 2'), (3, 'item 3');
    insert into item_attributes values (1, 1, 'att1 for item 1'), (2, 1, 'att2 for item 1');
    insert into item_attributes values (3, 2, 'att1 for item 2'), (4, 2, 'att2 for item 2');
    insert into item_attributes values (5, 3, 'att1 for item 3'), (6, 3, 'att2 for item 3');
    insert into attribute_values values (1, 'value for att 1'), (2, 'value for att 2'), (3, 'value for att 3'), (4, 'value for att 4'), (5, 'value for att 5'), (6, 'value for att 6');

    then, running the query i give as an example, i get this output:

    item_name 	attribute_name 	attribute_value
    item 1 	att1 for item 1 	value for att 1
    item 1 	att2 for item 1 	value for att 2
    item 2 	att1 for item 2 	value for att 3
    item 2 	att2 for item 2 	value for att 4
    item 3 	att1 for item 3 	value for att 5
    item 3 	att2 for item 3 	value for att 6
    
    Reply
  3. edesign

    Silly me, it was late last night I wasn’t looping through the mysql_fetch_assoc. Do you have an example of pivoting the data, I was thinking there was a way to do it with just MySQL alone? Any thoughts?

    Reply
  4. dschreck Post author

    You could always try something like:

    CREATE TABLE pivot_data (
    SELECT
    items.item_name,
    ia.attribute_name,
    av.attribute_value
    FROM
    attribute_values AS av
    JOIN item_attributes AS ia
    ON (ia.id = av.attribute_id)
    JOIN items AS items
    ON (items.id = ia.item_id)
    );
    

    Which will give you a bulk extraction, with this data you can preform “warehouse” queries. This again, just an example of how you can implement an EAV Like system.

    You can also load up this kind of bulk extraction as an array stored in memcache, which you update periodically, or create a view (MySQL 5)

    I wouldn’t really agree with creating a view, but it is a known way to optimize your performance while pivoting. Though you should take care and optimize your view’s, and use them sparingly.

    If I had to go either way, I would set up a cron or trigger system to update/create my pivot data sets in a batch process.
    Or even if the application allows it, have an update system which can be triggered only when needed.

    Hope that helps.

    Reply
  5. edesign

    Do you think creating the pivoted table via PHP and then storing it as a cached temporary table would make sense? It would have to be rebuilt via PHP if an edit to any of the existing elements happened?

    Reply
  6. dschreck Post author

    It would depend on your hardware set up.

    Having PHP build the pivot data per request could be extremely time consuming and CPU intensive. So I would recommend having Memcache sever set up, and then use another machine as a cron runner, which handles such things by periodically updating the memcache arrays.

    You could go extremely in depth and have it update when changes occur, and that’d just have to depend on how often your data set changes vs how often the cron runner runs.

    Again, this can be accomplished just the same by doing CREATE [or an INSERT INTO TABLE] ` ` SELECT ... queries – which you can also use MySQL Query caching if you could find a balance of: how often the data is updated, how often it needs to be re-pivoted, how ‘fresh’ does the content need to be.

    I prefer the first example I gave you, creating a pivot data table, which is just update in batch process or cron process.

    Reply
  7. spirit

    Hey, thanks for the article. I first attempt for me to understand EAV.

    Then with this structure would it be possible to construct a category tree built from one attribute. I mean all my item have 3 main attribute then a user would be allowed to have his category tree built from attribute1 or attribute2…

    Any thoughts ?

    Reply
  8. dschreck Post author

    Spirit:

    Technically EAV is the idea that only one entity can have any number of attributes, and one attribute can have any number of values.

    So what you’re describing sounds somewhat on track with this design pattern.

    Reply
  9. Web Resources.eu

    Very nice article.

    The EAV model you described is realy very simple and helpfull even for not expirienced users !

    Reply
  10. dschreck Post author

    Glad to see that people are finding this useful – hopefully I’ll be releasing more tutorials/walk through’s on more design patterns and PHP in the near future.

    Reply
Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *


− seven = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>