Meta Box

Custom table *per* CPT, or one table for all CPT's?

Support › MB Custom Table › Custom table *per* CPT, or one table for all CPT's?

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • July 28, 2022 at 6:17 PM #37255
    Digital RockeryDigital Rockery
    Participant

    Given a site with:

    1. At least 10 CPT's
    2. Some CPT's have as few as three custom fields in a single meta box
    3. Some CPT's have several dozen fields spread across multiple meta boxes
    4. We need to be able to relate some CPT's to each other with the "MB Relationships" addon
    5. We need to be able to relate some CPT's to each other via dropdown controls in one CPT editor that lists posts of another CPT to create a dependency effect of sorts (sometimes the list will be limited by a taxonomy term, and sometimes not limited, showing all posts in that CPT)

    What are the benefits and risks to using the "MB Custom Table" addon such that:

    • All fields from all meta boxes from all CPT's (and some pages) are handled in the same custom table, vs,
    • Fields from each CPT's meta boxes are stored in a separate table that is dedicated to those fields (that one CPT), plus,
    • One more custom table for storing field data from meta boxes that have been attached to specific pages or page groups?

    Do we lose any capabilities going with separate tables? Are there any performance gains or other benefits in this approach?

    July 29, 2022 at 10:26 PM #37272
    Long NguyenLong Nguyen
    Moderator

    Hi,

    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.

    July 30, 2022 at 12:54 AM #37275
    Digital RockeryDigital Rockery
    Participant

    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?

    July 30, 2022 at 1:49 AM #37276
    Digital RockeryDigital Rockery
    Participant

    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?

    August 1, 2022 at 1:14 PM #37296
    Long NguyenLong Nguyen
    Moderator

    Hi,

    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.

    Refer to this topic https://www.quora.com/How-many-columns-is-the-maximum-I-should-have-in-a-mySQL-Table

  • Author
    Posts
Viewing 5 posts - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.