Custom database tables

The WordPress course created the myplugin_bookings table with dbDelta() on activation. That gets a table onto one site. A plugin running on hundreds of sites also needs to change that table safely in version 1.1, 1.2 and 2.0, on sites that skipped versions and never re-ran activation. This lesson covers the decision to use a table at all, schema design, and a versioned upgrade path.

When meta is the wrong store

Post meta is a key and value store: every value is a row in wp_postmeta with a text column. It is ideal for a handful of values attached to a post. It becomes the wrong store when:

• The data is not about one post. A booking links an event, a user, a status and a time. Storing it as meta on the event means serialised arrays or dozens of keys.
• You filter or sort by several values at once. "Confirmed bookings for event 42 created this month" becomes several meta joins, each on an untyped text column.
• There are many records. Hundreds of thousands of bookings as meta rows make wp_postmeta huge, which slows every site query that touches meta, not just yours.
• You need aggregates. SUM(guests) over a typed column with an index is cheap; the same over serialised arrays is impossible in SQL.

Stay with posts and meta when the data is content: it needs an edit screen, revisions, the REST API, search or theme templates. Events stay a post type. Bookings, logs and analytics belong in a table.

Design the table for its queries

List the queries before writing the schema. For bookings:

Rules that keep tables healthy:

• Match WordPress's ID columns: bigint(20) unsigned for anything that stores a post or user ID.
• Store times as datetime in UTC, from current_time( 'mysql', true ), and convert for display. Sites change time zones; UTC does not.
• Use short varchar values from an allowlist for statuses, rather than free text.
• Add an index for each common WHERE and ORDER BY pattern, with the most selective equality column first. Every index slows writes slightly, so do not index everything.
• WordPress core does not use foreign key constraints, and dbDelta() does not manage them. Enforce relationships in code, for example by deleting an event's bookings when the event is deleted.

Version 1.1 of the schema

Version 1.1 adds an updated_at column and replaces the single-column indexes with the composite ones the queries need. The install function holds the complete, current schema; dbDelta() works out what to change:

<?php
// wp-content/plugins/myplugin/includes/install.php
defined( 'ABSPATH' ) || exit;

/**
 * Full table name for bookings on the current site.
 */
function myplugin_bookings_table(): string {
    global $wpdb;

    return $wpdb->prefix . 'myplugin_bookings';
}

/**
 * Create or update plugin tables to match the current schema.
 */
function myplugin_install_tables(): void {
    global $wpdb;

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $table           = myplugin_bookings_table();
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        event_id bigint(20) unsigned NOT NULL,
        user_id bigint(20) unsigned NOT NULL,
        guests tinyint(3) unsigned NOT NULL DEFAULT 0,
        status varchar(20) NOT NULL DEFAULT 'confirmed',
        created_at datetime NOT NULL,
        updated_at datetime DEFAULT NULL,
        PRIMARY KEY  (id),
        KEY event_status (event_id,status),
        KEY user_created (user_id,created_at),
        KEY created_at (created_at)
    ) {$charset_collate};";

    dbDelta( $sql );

    update_option( 'myplugin_db_version', MYPLUGIN_DB_VERSION, false );
}

And in the main file:

// wp-content/plugins/myplugin/myplugin.php
define( 'MYPLUGIN_DB_VERSION', '1.1' );

The schema version is separate from the plugin version on purpose. Most releases do not touch the database, and a separate number makes it obvious when one does.

What dbDelta does and does not do

dbDelta() parses your CREATE TABLE statement, compares it with the live table, and runs the CREATE or ALTER statements needed. It will:

• create the table if it does not exist;
• add missing columns and change column definitions that differ;
• add missing indexes.

It will not drop columns, rename columns or drop indexes. In the 1.0 table, KEY event_id (event_id) and KEY user_id (user_id) still exist after the upgrade. They are now redundant, and removing them needs an explicit statement in a versioned step, shown below.

Its parser is strict, and a formatting slip makes it silently do the wrong thing. The WordPress course listed the main rules; the full set from the Plugin Handbook:

• each column on its own line;
• two spaces between PRIMARY KEY and its column list;
• KEY rather than INDEX, and every key named;
• no backticks around names;
• lowercase column types, uppercase SQL keywords such as CREATE TABLE and NOT NULL;
• a length for every type that takes one, such as bigint(20).

dbDelta() returns an array of messages describing what it did. Log it while developing to confirm your changes were understood.

Running upgrades on every load

Activation does not run on updates, so check the stored version early on every request. It costs one autoloaded option read:

<?php
// wp-content/plugins/myplugin/includes/install.php
add_action( 'plugins_loaded', 'myplugin_maybe_upgrade_db' );

/**
 * Bring the database up to the current schema version.
 */
function myplugin_maybe_upgrade_db(): void {
    $installed = get_option( 'myplugin_db_version', '0' );

    if ( version_compare( $installed, MYPLUGIN_DB_VERSION, '>=' ) ) {
        return;
    }

    myplugin_install_tables();

    // Steps dbDelta() cannot do, each run once.
    if ( version_compare( $installed, '1.1', '<' ) ) {
        myplugin_upgrade_1_1();
    }
}

function myplugin_upgrade_1_1(): void {
    global $wpdb;

    $table = myplugin_bookings_table();

    foreach ( array( 'event_id', 'user_id' ) as $index ) {
        $exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, $index )
        );

        if ( $exists ) {
            $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP INDEX %i', $table, $index ) );
        }
    }
}

Notice:

• version_compare() compares versions properly, so 1.10 is newer than 1.9. A plain string comparison gets that wrong.
• myplugin_install_tables() is safe to re-run and brings any older site straight to the current schema, even one that skipped 1.1 and 1.2.
• Each manual step checks before it acts, so running it twice does no harm.
• The activation routine from the earlier lesson already calls myplugin_install_tables(), so new installs and upgrades end in the same state.

Two requests can arrive at once on a busy site right after an update, and both may start the upgrade. For schema changes that is usually harmless because every step is idempotent. Long data migrations need a lock and batching; the data migrations lesson builds that.

Wrap access in functions

Keep SQL for the table in one file, behind functions with clear names. The rest of the plugin calls the functions and never builds booking SQL itself:

<?php
// wp-content/plugins/myplugin/includes/bookings.php
defined( 'ABSPATH' ) || exit;

const MYPLUGIN_BOOKING_STATUSES = array( 'pending', 'confirmed', 'cancelled' );

/**
 * Create a booking. Returns the new ID, or 0 on failure.
 */
function myplugin_insert_booking( int $event_id, int $user_id, int $guests, string $status = 'confirmed' ): int {
    global $wpdb;

    if ( ! in_array( $status, MYPLUGIN_BOOKING_STATUSES, true ) ) {
        return 0;
    }

    $inserted = $wpdb->insert(
        myplugin_bookings_table(),
        array(
            'event_id'   => $event_id,
            'user_id'    => $user_id,
            'guests'     => $guests,
            'status'     => $status,
            'created_at' => current_time( 'mysql', true ),
        ),
        array( '%d', '%d', '%d', '%s', '%s' )
    );

    if ( false === $inserted ) {
        return 0;
    }

    $booking_id = (int) $wpdb->insert_id;

    wp_cache_delete( 'guests_' . $event_id, 'myplugin_bookings' );
    do_action( 'myplugin_booking_created', $booking_id, $event_id, $user_id );

    return $booking_id;
}

/**
 * Bookings for one event with a given status.
 *
 * @return object[]
 */
function myplugin_get_event_bookings( int $event_id, string $status = 'confirmed' ): array {
    global $wpdb;

    return $wpdb->get_results(
        $wpdb->prepare(
            'SELECT id, user_id, guests, status, created_at FROM %i WHERE event_id = %d AND status = %s ORDER BY created_at ASC',
            myplugin_bookings_table(),
            $event_id,
            $status
        )
    );
}

The payoff comes later. Cache invalidation and hooks such as myplugin_booking_created live in one place, and when the architecture lesson moves these functions into a MyPlugin\Bookings\Repository class, nothing outside this file needs to change.

Keeping related data consistent

Without foreign keys, clean up in code when an event is deleted permanently:

<?php
// wp-content/plugins/myplugin/includes/bookings.php
add_action( 'before_delete_post', 'myplugin_delete_event_bookings', 10, 2 );

function myplugin_delete_event_bookings( int $post_id, WP_Post $post ): void {
    if ( 'myplugin_event' !== $post->post_type ) {
        return;
    }

    global $wpdb;

    $wpdb->delete( myplugin_bookings_table(), array( 'event_id' => $post_id ), array( '%d' ) );
}

before_delete_post fires when a post is deleted permanently, not when it is moved to the trash, so trashed events keep their bookings until the trash is emptied.

Summary

• Use a custom table for many structured records you filter, sort or aggregate; keep content in posts and meta.
• Design columns and composite indexes from the queries you will run; store times in UTC and IDs as bigint(20) unsigned.
• Keep the full current schema in one dbDelta() call and store a separate schema version option.
• Check the version on plugins_loaded with version_compare(), re-run dbDelta(), and add idempotent manual steps for drops and renames.
• Put all SQL for a table behind a few functions, and clean up related rows in code.