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.
Best Answer
To my knowledge there are these 2 ways you can go:
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.
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.