Does WordPress database architecture need improvement?

WordPress uses the meta_key, meta_value to store values of an entry.

You have to run multiple subqueries to get filter records based on metadata values.

Anyone else felt that the way WordPress stores post metadata could be improved?

Comments

[Ihor Vorotnov]

Well, the idea of WP's database design is that you don't actually use metadata (records from wp_*meta tables) to query the data. If you have an object property (post / post type's property) that you need to filter by, make it a taxonomy. Metadata is intended to be just an additional key-value storage for all the extra data. If your data comes with a lot of such properties, you might better consider a dedicated custom database table with foreign key post_id, or use metadata to store them but never query directly - instead opt for something like Elastic Search.

[Lax Mariappan]

How an object property get its values? Still from sub-queries right? For example WooCommerce stores a large amount of data per order, product etc as meta key & values which could be saved as a flat table.

PrestaShop saves order data much effectively and its easy to access through object properties and performance is much better.

Drupal creates tables for every field and their revisions, a better approach.

I had to store records with post_ID and associated values as meta_data. They are not hierarchical or can be grouped as taxonomy, so I ended up creating a flat table to store records instead of custom post type and custom meta fields.

Though many improvements in the frontend, WordPress core architecture could be improved too my two cents.

[Ihor Vorotnov]

How an object property get its values? Still from sub-queries right?

Not sure I understand what you mean.

For example WooCommerce stores a large amount of data per order, product etc as meta key & values which could be saved as a flat table.

WooCommerce actually uses own tables:

thepracticaldev.s3.amazonaws.com/i...

PrestaShop, Drupal and other CMS/CMF/Frameworks use different DB schema. You shouldn't compare them directly, it's pointless.

so I ended up creating a flat table to store records instead of custom post type and custom meta fields

And that is absolutely fine. You're free to create your own tables if that makes your life easier/better.

Though many improvements in the frontend, WordPress core architecture could be improved too

Oh sooooo true! I wholeheartedly agree on this. There's so much to improve in WP core... And DB schema is a minor issue, to be honest.

[Lax Mariappan]

An interesting read related to this reddit.com/r/Wordpress/comments/ee...