Database design for dynamic Meta fields of an entity that shall be queried intensively

Question

I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, boolean or string) are dynamically entered by admins.

At the end of the data population, the admin will be able to create queries to filter out the entities based on meta values.

Normally, I would follow wordpress's DB structure of having meta table of the following structure.

Meta => id, entitiy_id, meta_key, meta_data_type, meta_value

However, I'm concerned about the performance and the query-ability of the data since I expect it to be more intensive. Not to mention that I'll be using mysql for the DBMS.

Please advice if there is a better practice than this. If not, is there any pitfalls that I should take note of before I develop this structure.

Answer 1

To my knowledge there are these 2 ways you can go:

1. If you want performance, simpler queries, easier programming, then you should make a second table with your ID as foreign key and make there a column for each of your properties that you want to store. For any new attribute you would have to change the database schema, what might be a major drawback.

2. The above won't work, if you need flexibility in your database schema (because your meta properties are not known in advance or are point to change), but it will add to your code and queries complexity (depending on your framework/application) and also lead to poor performance compared to the first alternative. Poor performance can be compensated to a certain extent with more hardware, but that's expensive.

It's a trade off between the two. You must judge your application scenario and decide what you need most and what is a absolute no-go. Then decide on the remaining criteria what you want to use.

I have seen and used both ways I mentioned above, and must say the second one worked better for me (didn't have high volume querying though), even if it is a antipattern from a SQL / database normalization point of view. However, we aren't payed for nice SQL database designs, but for working applications.

Answer 2

I would seriously advise against this design. Its a database anti-pattern called the Entity Attribute Value pattern.

Here are some posts as to why:

http://karwin.blogspot.com/2009/05/eav-fail.html

http://www.jonathanlevin.co.uk/2008/04/sql-is-in-fact-programming-language.html

A better approach would be to start with the columns/attributes you have and add them at a later stage.

You can run ALTER TABLE statements to make the changes later and there are tools to do this in an "online" manner as well.

http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html

Other things you can do is use a document-based database or XML. You can also store XML documents in MySQL in blob/text files, but you lose a lot of benefits this way.