ClassicPress PetitionsClassicPress Petitions
This is a read-only archive. Post or comment on the live version of this page on our forums.

DB structure: each CPT should have their own datatable

December 2, 2018 · 03:39 · Peter B
Description

As we all now in case of CPTs WP stores all the custom fields in post_meta table, which works of course but is far from being ideal. To show render any custom data for the users WP needs to look through 2 tables or join them. Or if the admin adds later a new custom field to a CPT those land at the end of post_meta table making that whole table a complete mess of randomly mixed data. Even worse if one does not need a custom field in a CPT anymore, well then those data remain in post_meta as orphans.

IMHO instead of all the above each CPT should have its own table in the DB. Everything would be much better organized and more flexible that way (one could add or delete rows anytime to any CPT without leaving garbage in post_meta). Even queries would be faster. I do not see cons at the moment.

Voters
+17 more
Discussion
Greg Schoppe

This would certainly be a better system to have started with, but at this point, switching to such a system would break compatibility with pretty much every major theme and plugin... it seems like a heavy cost for this functionality.

Perhaps we could go about this in a less destructive way, by initially creating a framework class that defines "objects", creates dedicated core and meta tables for them, and provides basic CRUD operations and reasoning about these objects.

We could then refactor WP_Post, WP_Term, WP_User, etc to inherit from this core object class, as children that extend the WP_Object class with a table slug, and various requirements for additional fields, WP_Query parameters, admin interfaces, etc.

Finally, we could then make it easy to extend these Object types again, into new Objects with all the same functionality, but with different tables, or different field needs (beyond the basic common fields assigned in the base object).

If we did this right, you could easily create "WP_Post-like Objects" that have dedicated tables.

That way we would maintain full back-compatibility, but it would be easy for plugins like WooCommerce to leverage all the capabilities of WP Post, without muddying the common table with user data like orders.

James Nylen

> Perhaps we could go about this in a less destructive way, by initially creating a framework class that defines "objects", creates dedicated core and meta tables for them, and provides basic CRUD operations and reasoning about these objects.

Is there an advantage to this approach over (for example) adding a new argument db_table to register_post_type, and having it default to $wpdb->posts? This wouldn't be as comprehensive, but it also wouldn't need as many changes to the underlying code.

invisnet

I think the new argument to register_post_type is a better solution, but it should be a boolean - the table name should be derived from $post_type.

Greg Schoppe

the reason I suggest post-like objects rather than posts with a new parameter, is that the parts of WordPress that relate to posts, such as Terms and/or plugins consider them as being keyed only by ID. There isn't an easy way to move specific post types to a new table, without running into ID collisions with the existing posts.

So, for example, wp_term_relationships stores the object_id as the only reference to the post that is in a taxonomy. So, if you create a taxonomy that is attached to two post types, one in the wp_posts table, and one in the wp_cpt_services table, you cannot guarantee that there will not be ID collisions between the two tables.

Using post-like objects for these situations would preserve existing functionality while allowing developers to easily move forward with better practices. The delineation between posts and post-like objects would make it clear that they are separate entities that cannot be interspersed with posts in every expected way.

Greg Schoppe

Not to mention refactoring the basic object types to be proper oop abstractions would also allow for much more flexibility for future plugins to build structures that don't have to be completely coded from scratch, but can still be optimized for specific use cases.

Raymund

Pods plugin allow for custom fields to be saved in a separate table per CPT. But data is still defined as CPT with entries in the posts table. Some CPT's might need to be saved in post_meta to be compatible with other plugins. Maybe a UI to create CPT, then choose whether to save as in post_meta or separate table.

James Nylen

@Greg:

>the reason I suggest post-like objects rather than posts with a new parameter, is that the parts of WordPress that relate to posts, such as Terms and/or plugins consider them as being keyed only by ID.

Good point, this is something I hadn't considered. Given that, I think your suggested approach is likely the simplest way forward.

That's not to say it would be easy to pull off, at least without giving up a lot of functionality for these new objects. By itself, creating WP_Post-like objects doesn't solve the issues with creating a taxonomy that applies to multiple post types that may have ID collisions. There are other, similar cases (parent post handling is one, and I'm sure there are more).

Greg Schoppe

> creating WP_Post-like objects doesn't solve the issues with creating a taxonomy that applies to multiple post types that may have ID collisions.

This is true, but by making them post-like objects, rather than posts, we would prevent users from expecting this behavior from the existing objects.

Daniele Scasciafratte

I think that we can start doing that with custom fields using the metadata api https://codex.wordpress.org/Metadata_API
If a dev use qp_query should don't have problems with forcing that.