EAV vs Serialized Object vs SQL with Xpath?

Question

I'm trying to implement a badge system, the badge are based on user's metadata which are subject to change.

Those metadata are variable, and are set on the fly.

Example of metadata :

• commentCount
• hasCompletedProfile
• isActiveMember

etc. Later, I would want to add hasGravatar metadata, for this reason, I can't easily design and normalize a table.

Those data, while they are an important part of the application are not 'sensible', almost all those metadata could be re-computed that means that the integrity of the data is not part of constraints.

Currently, I know three options, even if I didn't know any of them.

• EAV
• Serialized Objects
• XML Field (I read somewhere that it is possible to store XML in a column, and use XPATH or something to query data)

All of these options look to have pro & cons but since I've never experimented with them, I don't really know which.

Do you have any feedbacks or advices?

I'm currently working with Zend Framework & Doctrine 2 with a MySQL server

Answer 1

XML and Serialized Objects are both very similar as you would likely be using 1 column to store this arbitrary data. This quickly becomes very messy and difficult to easily distinguish in SQL WHERE clauses (though some DBMS have XPath support)

EAV on the otherhand will provide a separate row for every Key => Value pair you have, which you can easily extract out with a JOIN or subquery. The major downfall is that it can be a performance hit if you have a lot of data in here. Another drawback is that to keep things simple you would store all keys/values as text in the db. You could create an EAV table for every type, but it's not practically needed in most languages as what you fetch comes out as a string or can be converted there anyway. Simply storing user configuration/properties should be perfectly fine for EAV.

So you might have a table user_metadata with 3 fields:

metadata_id INTEGER
user_id INTEGER
key CHAR
value CHAR

You could then fetch this data all at once for a user:

SELECT * FROM user_metadata WHERE metadata_user_id = $user_id

Or you could fetch individual metadata along with your user data

SELECT user.*, meta_gravatar.value AS hasGravatar
FROM user
LEFT JOIN user_metadata AS meta_gravatar
 ON meta_gravatar.user_id = user.user_id AND meta_gravatar.key = 'hasGravatar'
WHERE user.user_id = $user_id