Building an Entity-Attribute-Value System - Part I - Understanding EAV

Posted on January 08, 2014 in php, codeignitor, eav, sql, database

Introduction

I recently did some digging through my hardrive and came across a CodeIgnitor project I never got around to finishing (surprise, surprise.) I've since rewritten the project in another framework but I'm glad I saved the source code because I had completely forgotten that at one point I attempted to recreate Magento's EAV system, complete with indexer, in CodeIgnitor and actually got pretty far. Instead of burying the code and laughing at my petty mistakes, I'm going to bring light what EAV is all about and why, after a bit of practice, it's actually not as scary as one might think.

Word of caution: These articles should be taken with a grain of salt. The series should serve as a demonstration and practical learning example and is not recommended to be used in any production environment.

Understanding EAV

Why use EAV?

I've heard all the stories, EAV is slow, it's not agile, blah-blah-blah. I know EAV is not the most optimized system in the world, but there is a lot you can do to make it more adaptable in high-traffic applications (we'll get into Indexing later.) I love working with EAV (namely Magento) because it allows an end-user to adapt a website to their needs with little to no knowledge of code infrastructure. This is extremely powerful when it comes to time and cost management. Is it more effective to hire a programmer for 5 hours to add a few columns across a website or is it easier to click a few buttons and have it done for you? I believe in the latter (despite being a paid programmer.)

How does EAV work?

The EAV model, while itself is very complex, can be explained quite easily. Let's take a working example.

You have a model user, this model contains a few attributes such as

  • Name
  • Username
  • Bio
  • Phone #

Each of these attributes can be assigned to a certain database type.

  • Name - varchar(255)

  • Username - varchar(32)

    • Bio - text
  • Phone # - int(11)

    If you know anything about databases, then the above should seem pretty obvious. The difference between a traditional database table and the ones used by EAV is that in an EAV model, the attributes are stored across an assortment of tables, each with their own type. For example, the "Name" attribute would most likely be stored in a table like attribute_values_varchar and "Phone" in a table like attribute_values_int. Make sense? Instead of storing our user as a flat table, we store it across several different tables.

Holy optimization, Batman. This sounds crazy

You're absolutely correct, it is. As you can probably already guess, this system is insanely inefficient when used in any practical application. Sure, there is a lot of room for improvement such as SQL joins and proper table indexes, but where's the fun in that? Instead, I've opted for the Magento approach and built a custom indexer that both collects a model and it's attributes and stores them in one big flat table.

So where do we start? Stay tuned for the next blog post when we start rolling out database tables and begin the implementation.

Cheers!

Thomas Lackemann :)

About

Tom is the founder of Astral TableTop. He's a homebrewer, hiker, and has an about page. Follow @tlackemann on Twitter for more discussions like this.

Comments