WordPress stores almost everything in wp_posts and wp_postmeta, but high-volume plugins — analytics, logging, event ticketing — need dedicated tables with proper normalisation and indexed columns. The dbDelta() function handles schema creation and migration safely: it creates new tables, adds missing columns, and changes column types without destroying existing data, making it the correct tool for plugin activation and upgrade routines.

Problem: A WordPress plugin stores structured data — event registrations, API usage logs, subscription records — in wp_postmeta or wp_options because creating a custom database table seems complex and fragile across plugin updates.

Solution: Use dbDelta() from wp-admin/includes/upgrade.php in the plugin activation hook to create the custom table safely. dbDelta() compares the desired schema against the existing one and issues only the necessary ALTER TABLE statements — it is safe to run on every activation. Store the schema version in wp_options and call dbDelta() again on plugin update when the version changes.

The code below creates a custom table on plugin activation with dbDelta(), runs schema migrations on upgrade by comparing a stored version number, adds a typed data-access layer, and shows how to clean up on uninstall.

prefix . 'my_events';
    $charset = $wpdb->get_charset_collate();

    // dbDelta requires very specific formatting:
    // - Two spaces between column name and type
    // - PRIMARY KEY on its own line (not inline)
    // - No trailing comma on the last column before the closing paren
    $sql = "CREATE TABLE $table (
  id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id     BIGINT(20) UNSIGNED NOT NULL,
  event_type  VARCHAR(50)         NOT NULL,
  event_data  LONGTEXT,
  created_at  DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY idx_post  (post_id),
  KEY idx_type  (event_type),
  KEY idx_date  (created_at)
) $charset;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql );

    update_option( 'my_plugin_db_version', '1.0.0' );
}

// ── 2. Run migrations on plugin update ────────────────────────────────────
add_action( 'plugins_loaded', function () {
    $installed = get_option( 'my_plugin_db_version', '0' );
    if ( version_compare( $installed, '1.1.0', '<' ) ) {
        global $wpdb;
        $table = $wpdb->prefix . 'my_events';
        $charset = $wpdb->get_charset_collate();

        // Adding a new column — dbDelta handles this non-destructively
        $sql = "CREATE TABLE $table (
  id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id     BIGINT(20) UNSIGNED NOT NULL,
  event_type  VARCHAR(50)         NOT NULL,
  event_data  LONGTEXT,
  user_id     BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  created_at  DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY idx_post  (post_id),
  KEY idx_type  (event_type),
  KEY idx_user  (user_id),
  KEY idx_date  (created_at)
) $charset;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
        update_option( 'my_plugin_db_version', '1.1.0' );
    }
} );

// ── 3. Typed data-access layer ────────────────────────────────────────────
class My_Events_Table {

    private static function table(): string {
        global $wpdb;
        return $wpdb->prefix . 'my_events';
    }

    public static function insert( int $post_id, string $type, array $data = [], int $user_id = 0 ): int|false {
        global $wpdb;
        $result = $wpdb->insert(
            self::table(),
            [
                'post_id'    => $post_id,
                'event_type' => $type,
                'event_data' => wp_json_encode( $data ),
                'user_id'    => $user_id ?: get_current_user_id(),
            ],
            [ '%d', '%s', '%s', '%d' ]
        );
        return $result ? (int) $wpdb->insert_id : false;
    }

    public static function get_for_post( int $post_id, string $type = '', int $limit = 20 ): array {
        global $wpdb;
        $table = self::table();
        $sql   = $wpdb->prepare(
            "SELECT * FROM `$table` WHERE post_id = %d" . ( $type ? ' AND event_type = %s' : '' ) . " ORDER BY created_at DESC LIMIT %d",
            ...array_filter( [ $post_id, $type ?: null, $limit ] )
        );
        return $wpdb->get_results( $sql, ARRAY_A ) ?: [];
    }
}

// ── 4. Remove table on uninstall (uninstall.php) ──────────────────────────
// if ( ! defined( 'WP_UNINSTALL_PLUGIN' ) ) exit;
// global $wpdb;
// $wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}my_events" );
// delete_option( 'my_plugin_db_version' );

NOTE: dbDelta() can only add columns and indexes — it cannot drop columns, rename columns, or change a column's position; if your migration requires removing a column, run a raw ALTER TABLE ... DROP COLUMN statement after dbDelta(), and always back up the table before running destructive migrations on production.

WordPress’s post/meta/option tables cover the majority of storage needs for themes and plugins. But sometimes you genuinely need a custom relational table — for high-volume event logs, complex many-to-many relationships, analytics data, or any structure where the EAV (entity-attribute-value) pattern of wp_postmeta would produce hundreds of joins. WordPress provides dbDelta() for exactly this: a function that compares a desired table definition against the current database schema and applies only the necessary changes — creating the table if it does not exist, adding missing columns, and changing column definitions, all without dropping existing data. Understanding how to use it correctly — including its strict whitespace requirements that trip up most developers on first use — is the key to writing reliable plugin installers.

Problem: Your plugin needs a dedicated database table to store structured data that does not fit the post/meta model. The table must be created on activation, updated safely when the plugin upgrades, and removed on uninstall.

Solution: Use dbDelta() inside the activation hook to create and migrate the table. Call it again on upgrade by comparing stored and current schema versions.

<?php
function my_plugin_create_tables() {
    global $wpdb;

    $table   = $wpdb->prefix . 'event_log';
    $charset = $wpdb->get_charset_collate();

    // CRITICAL: dbDelta requires exactly TWO spaces between the column name
    // and its definition, and a comma at the END of every line except the last.
    // Each KEY line must be on its own line. Any deviation silently fails.
    $sql = "CREATE TABLE $table (
  id         bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  event_type varchar(100)        NOT NULL,
  object_id  bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  extra_data longtext                     DEFAULT NULL,
  created_at datetime            NOT NULL,
  PRIMARY KEY  (id),
  KEY user_id (user_id),
  KEY event_type (event_type)
) $charset;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql );
}

register_activation_hook( __FILE__, 'my_plugin_activate' );

function my_plugin_activate() {
    my_plugin_create_tables();
    add_option( 'my_plugin_db_version', '1.0' );
}

// Run upgrade check on every admin page load
add_action( 'plugins_loaded', 'my_plugin_upgrade_check' );

function my_plugin_upgrade_check() {
    $current = get_option( 'my_plugin_db_version', '0' );
    if ( version_compare( $current, '1.1', '<' ) ) {
        // dbDelta will ADD the new column without touching existing data
        my_plugin_create_tables();
        update_option( 'my_plugin_db_version', '1.1' );
    }
}

Querying the custom table safely with $wpdb->prepare():

<?php
function get_user_events( $user_id, $limit = 20 ) {
    global $wpdb;
    $table = $wpdb->prefix . 'event_log';

    return $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE user_id = %d ORDER BY created_at DESC LIMIT %d",
            absint( $user_id ),
            absint( $limit )
        )
    );
}

NOTE: dbDelta() can only add columns and indexes — it never removes them. If you need to drop a column in a plugin upgrade, you must run the ALTER TABLE statement separately after calling dbDelta(). Also, dbDelta() is strict about the SQL format: the PRIMARY KEY definition must have two spaces between the words, and there must be no trailing comma after the last column definition before the closing parenthesis.