Given a site with:
What are the benefits and risks to using the "MB Custom Table" addon such that:
Do we lose any capabilities going with separate tables? Are there any performance gains or other benefits in this approach?
I think the case storing each meta box and its custom field values in a separate custom table would be better. The custom table stores each post ID in a row and each cell is a field value so if you store all fields of all CPTs in a custom table, there are too many columns in that table and will affect the performance of query posts which you can read here https://docs.metabox.io/extensions/mb-custom-table/#query-posts-with-wp_query
There are no different or limited capabilities when storing custom fields of a meta box in a separate table.
Thank you so much for this info.
To be clear, my plan was not to store fields from each meta box in a dedicated table (one table per meta box). Rather, I was asking about all fields of all meta boxes associated with a particular CPT in a dedicated table (one table per CPT, in essence, rather than one table per meta box).
Does this change your answer at all?
To be clear, I'm also open to having one table per meta box, but if that's overkill, the middle ground in my original plan seems quite maintainable.
Which raises a related question: At one point do we see a benefit to splitting storage into additional fields/columns? 20 columns? 100?
There is no exact answer for how many columns should be used. It depends on what data you store, how powerful your hosting/server is, and how much time you will spend maintaining the table. I see there are some benefits:
- Time to maintain
- Time to query
- Delete one table if you do not want to use a meta box instead of deleting each column relate to each field of a meta box in a table.