Advanced Custom Database Tables: Schema, Migration, and Indexing

Previously in this course, we established the Modern PHP Standards for WordPress and explored Dependency Injection Basics to ensure our code remains modular and testable. While wp_postmeta is the default WordPress storage pattern, it is essentially an EAV (Entity-Attribute-Value) model that becomes a performance bottleneck as data volume grows.

In this lesson, we shift our focus to MySQL and Database Architecture by implementing custom tables for our Knowledge Base plugin. This allows us to define rigid schemas, leverage native indexing, and significantly reduce join complexity.

The Case for Custom Tables

When building a Knowledge Base, you are dealing with structured data: categories, article versions, author metadata, and view counts. Storing these in wp_postmeta forces the database to perform multiple self-joins for every query. By moving to a custom table, we gain:

• Predictable performance: Fixed-length columns allow the engine to optimize storage.
• Type safety: Native MySQL types (INT, DATETIME, JSON) enforce data integrity.
• Efficient Indexing: We can index specifically on the columns we query most frequently.

Writing SQL Migration Scripts

In WordPress, we don't execute raw CREATE TABLE commands on plugin activation. Instead, we use dbDelta(). This function parses your SQL, compares it against the existing schema, and executes the necessary ALTER TABLE statements to update the schema without destroying data.

Worked Example: Defining the Schema

Let's define a kb_articles table. Create a file in src/Database/Schema.php:

PHP
namespace KnowledgeBase\Database;

class Schema {
    public static function get_schema(): string {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        return "CREATE TABLE {$wpdb->prefix}kb_articles(
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL,
            status VARCHAR(20) DEFAULT 'draft' NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY(id),
            KEY status_index(status),
            KEY slug_index(slug)
        ) $charset_collate;";
    }
}

Implementing dbDelta for Updates

You must trigger dbDelta during the plugin activation hook. It is critical to include the upgrade.php file, as dbDelta is not loaded by default.

PHP
public function activate() {
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    \dbDelta(\KnowledgeBase\Database\Schema::get_schema());
}

Defining Efficient Table Indexing

Indexing is the most critical aspect of database architecture. While primary keys are indexed automatically, you must manually define indexes for columns used in WHERE, ORDER BY, or JOIN clauses.

Indexing Best Practices

• Cardinality: Only index columns with high cardinality (many unique values). Indexing a status column with only "draft" and "published" values is often counter-productive.
• Covering Indexes: If you frequently query SELECT title FROM kb_articles WHERE slug = %s, an index on (slug, title) allows the engine to retrieve the data directly from the index tree without touching the table rows.
• Avoid Over-indexing: Every index adds overhead to INSERT and UPDATE operations. Only index what you actually query.

For our Knowledge Base, we might add a composite index if we often filter by status and date: KEY status_date_index (status, created_at)

Hands-on Exercise

1. Create a second table, kb_article_stats, to store article_id (bigint), views (int), and last_viewed (datetime).
2. Add a FOREIGN KEY reference to the kb_articles table.
3. Use dbDelta to apply this change. Note: Remember that dbDelta is sensitive; ensure your SQL formatting matches the WordPress coding standards exactly (e.g., two spaces before PRIMARY KEY).

Common Pitfalls

• The dbDelta Formatting Trap: If your SQL formatting is not precise (e.g., missing spaces or using incorrect casing for types), dbDelta may fail to recognize the table and attempt to recreate it, resulting in a database error.
• Ignoring Character Sets: Always include $wpdb->get_charset_collate() to prevent collation mismatches, which can cause subtle bugs when performing joins with core tables.
• Large Table Migrations: If your table already holds millions of rows, running dbDelta can lock the table. In production, consider performing schema changes via a dedicated migration runner rather than plugin activation.

Recap

We have moved beyond the flexibility of wp_postmeta to the performance of custom tables. By using dbDelta, we ensure our schema remains in sync as our plugin evolves. Proper indexing remains our strongest tool for maintaining speed as our Knowledge Base grows.

When dealing with more complex data, consider reading Advanced Database Queries: Mastering SQL and Performance in WordPress for deeper insights into optimization.

Up next: Data Access Objects Pattern — we'll wrap these tables in repository classes to decouple our business logic from raw SQL queries.