Should You Use EAV?

The Entity-Attribute-Value (EAV) pattern can be used to flexibly add or remove properties to an object and its corresponding data model. There is some debate about if and when it is appropriate to use EAV. I’ll provide my opinions here, and also a tutorial on how to implement EAV into a Laravel 5.1 application using Cartalyst’s Attributes package.

The Basics

EAV allows you to attach properties to an object with name/value pairs. For example, lets say that I have a User object, and I’d like to add some metadata that is not in the base model definition. Perhaps the base model doesn’t have a property for a mobile phone number. I’d like to be able to add that by providing a property name and value like mobile=555-867-5309. EAV allows you to do this by simply adding one or two tables to your database schema. In MySQL format, the table definition might look something like:

[sql]

CREATE TABLE `attributes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`value` mediumtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `attribute_user_id_name_index` (`user_id`,`name`),
);

[/sql]

As you can see, the table schema is just four columns. It associates a name/value pair with a user via the user_id. We can add as many name/value pairs to this table as we’d like. In this case I’m forcing each row to have a unique user_id/name pair so that you can’t have more than one value for the same piece of metadata. It is possible to make this more generic so that you can add metadata to any type of object using just one table, but I wanted to keep this example as simple as possible.

Pitfalls

Some people consider EAV an anti-pattern, and ask the reasonable question of why not just add the fields you need to the main table or in a more traditional relational structure?Indeed, there are certain situations, and certain reasons why you might not want to use EAV. Most of them boil down to code complexity (i.e. query complexity) and performance. The example above is relatively straightforward, and EAV usually works okay when you restrict yourself to using scalar values, but you can imagine the added complexity of writing a SQL query to retrieve and parse many different values. Also, things get tricky if you try to store more complex things in the value field, like serialized arrays or objects.

If you are working with thousands of objects, using EAV really slows down performance. I tried to use it for a WordPress plugin once that stored metadata for about 25,000 people. When I would pull up the screen that listed all of the people, even though they were paged the screen could take over a minute to load! I eventually realized that WP was not the right tool for the job and built a standalone app to accomplish my task.

When to use EAV

Haters are gonna hate, and some people will scream that you should never use EAV, but I think EAV is actually the right tool for the job in a couple of situations.

Creating Cowpaths During Development

Right now I’m building an app called Bountify, and although I know I’m going to want a user profile that provides more than just a simple name and email address, I don’t actually know exactly what fields I’m going to need/want. In this case, I plan to use EAV during the development phases so that I can add fields to the user profile flexibly. Once I’ve settled on a relatively stable set of fields, I’ll pave the cowpaths, i.e. I’ll modify the data model of the app to use a more traditional schema that performs and scales well.

Simple Stuff or You Have No Choice

Sometimes, you really have little choice. For example, WordPress uses the EAV pattern to support the adding of metadata to themes and plugins. If you’re developing a plugin for WP, unless your data model is very complicated, the cost of adding new tables to their database is usually more trouble than its worth. There are millions of WP users, and there are thousands of themes and plugins that have been developed. Many, if not most, of them use WP’s EAV-based options table to store their data. Given that WP is perhaps the most widely installed CMS on the market, that should be enough evidence to convince you that EAV is not always bad.

Incorporating EAV into Laravel 5.1

I only found one freely available EAV package for Laravel, but since it had only been downloaded six times when I looked at it, I decided it probably wasn’t mature enough to incorporate into my project. I happen to have a subscription to the many excellent packages provided by Cartalyst, and it turns out one of those is a package for adding EAV to composer based projects.

Installation

At the time I write this post, Attributes has still not reached a stable release. However, it’s basic functionality seems to work well so far. Their installation instructions are a little outdated and don’t work with Laravel 5.1 because the --package option has been removed from the artisan migrate command. After you have downloaded the package using composer, the simplest thing to do is to just copy the contents of vendor/cartalyst/attributes/src/migrations/ into your database/migrations/ folder and then run php artisan migrate.

Of course, I’m never into simple. If you’d like you can follow these modified instructions to use my patch that updates the package for use with Laravel 5.1. First in the require array in your composer.json file add:

[javascript]"cartalyst/attributes": "dev-laravel"[/javascript]

And then add the following after the require array:

[javascript] "repositories": [
{
"type": "vcs",
"url": "https://github.com/morphatic/attributes"
}
] [/javascript]

Then you can run composer update and then php artisan vendor:publish and finally php artisan migrate. This will automatically copy the migration to the correct location.

Usage

Although the Attributes documentation is relatively straightforward, they don’t address the question of where or when to create your new attributes from within a Laravel project. Since I was adding metadata to the user model, I chose to update the constructor in the User class:

[php] use Cartalyst\Attributes\Attribute;
use Cartalyst\Attributes\EntityTrait;
use Cartalyst\Attributes\EntityInterface;

class User extends EloquentUser implements EntityInterface
{
use EntityTrait;

public function __construct(array $attributes = [])
{
/*
* EAV Attributes
*/
Attribute::firstOrCreate([‘slug’ => ‘gender’]);

// call parent constructor
parent::__construct($attributes);
}
}
[/php]

A couple of things to note:

1. The Attribute class is derived from the Eloquent\Model class which means you can use all of the typical functions associated with your other models. In this case, I used the firstOrCreate() method to create the attribute because since this code runs every time the constructor is called, it will throw an error if it tries to create a duplicate.
2. I called the parent::__construct() method after creating the attribute so that it would be available even the first time I wanted to use it.

The cool thing about this package is that once it’s installed you can access and save attributes exactly as you would any other property of your model.