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.

Post navigation

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

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?

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?

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.

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?

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.

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…