Why custom table for custom fields?

By default, WordPress uses meta tables for storing the meta data (custom fields). In order to satisfy a wide range of needs, WordPress uses a flexible database structure, where each custom field is stored as a row with 4 columns: meta_id, post_id, meta_key and meta_value. While it seems to be fine at first, when your data grows, the number of database rows increases very fast.

Let’s take an example: If you’re using WooCommerce, each order (a custom post type) requires more than 40 custom fields as follows:

Imagine if you run a flower shop, and have 20 orders per day, then WordPress will store 20 * 40 = 800 rows in your database. After 1 month, it will be 30 * 800 = 24.000 rows, for just 30 * 20 = 600 orders.

What if we can store all the custom fields in a single row? Something like in the first screenshot, where each custom field takes only 1 column in the database. Therefore, with the case of WooCommerce orders, you only have 600 rows in the database for 600 orders. Besides, all the information about a single post can be viewed at once.

WHAT ARE THE BENEFITS OF THIS METHOD?

• Your database size will reduce
• As all the custom field data is on the same row, your query will be faster and simpler
• You can view all the data at once using any database management tool
• You can export all the data easier by exporting only one table

In order to achieve these goals, MB Custom Table extension is all you need.

MB Custom Table provides a simple API for you to create custom tables and store custom fields to custom tables. It works with all field types of Meta Box plugin as well as all meta types (post, user, term). The extension uses a simple syntax to define the table and you won’t have to change your meta box definition.

See how to save WordPress custom fields in custom tables

Custom models

Saving custom fields in a custom table is great. However, you still left a row in the default WordPress table for the custom post type. So, you have two rows in the database – one for the post and one for the custom fields.

Since version 2, the plugin allows you to create custom models, which allows you to define all fields and save them all in one single custom table. You can view a list of items, edit or delete them. It also works with MB Admin Columns which allows you to define custom columns for the table list. It also works with most of Meta Box plugins.

This is a quick video showing the overview of custom models:

Features

• Uses WordPress recommended method to create custom tables, which allows you to upgrade table structure anytime.
• Provides a simple API to define table structure.
• Works with any meta types: post meta, user meta, term meta, comment meta.
• Provides an easy way to integrate with your existing meta boxes.
• Support creating custom models which combines the post and post meta table into one.