Full Journey: Dynamic DB Table Creation with Migration Classes + Builder Pattern

1. Objective

Build a scalable, maintainable, and testable database system for the Radius Booking plugin using class-based migrations and a dynamic SQL builder.

2. Architecture Goals

• Modular: One class per table (SRP).
• Reversible: Each class has up() and down() methods.
• Dynamic: Use a builder to generate SQL.
• Extensible: Add tables via simple class creation.
• Composer + PSR-4: Autoloading and organization.

---

3. Folder Structure

radius-booking/
├── app/
│   ├── Database/
│   │   ├── Migrations/
│   │   │   ├── CreateAppointmentsTable.php
│   │   │   ├── CreateServicesTable.php
│   │   │   └── ...
│   │   ├── TableBuilder.php
│   │   ├── MigrationInterface.php
│   │   └── MigrationManager.php

---

4. Step-by-Step

Step 1: Migration Interface

interface MigrationInterface {
    public function up(): void;
    public function down(): void;
}

Step 2: TableBuilder Class (Fluent SQL Builder)

class TableBuilder {
    protected string $table;
    protected array $columns = [];
    protected array $keys = [];
    protected string $charsetCollate;

    public function __construct(string $table, string $charsetCollate) {
        $this->table = $table;
        $this->charsetCollate = $charsetCollate;
    }

    public function addColumn(string $name, string $type): self {
        $this->columns[] = "$name $type";
        return $this;
    }

    public function addPrimary(string $column): self {
        $this->keys[] = "PRIMARY KEY ($column)";
        return $this;
    }

    public function addUnique(string $column): self {
        $this->keys[] = "UNIQUE KEY $column ($column)";
        return $this;
    }

    public function build(): string {
        $sql = "CREATE TABLE {$this->table} (\n    " .
               implode(",\n    ", array_merge($this->columns, $this->keys)) .
               "\n) {$this->charsetCollate};";
        return $sql;
    }
}

Step 3: Create a Migration Class (e.g., Services)

class CreateServicesTable implements MigrationInterface {
    protected $wpdb;
    protected $table;
    protected $charsetCollate;

    public function __construct($wpdb, $prefix, $charsetCollate) {
        $this->wpdb = $wpdb;
        $this->table = $prefix . 'services';
        $this->charsetCollate = $charsetCollate;
    }

    public function up(): void {
        $builder = new TableBuilder($this->table, $this->charsetCollate);
        $sql = $builder
            ->addColumn('id', 'BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT')
            ->addColumn('name', 'VARCHAR(191) NOT NULL')
            ->addColumn('description', 'TEXT NULL')
            ->addColumn('duration', 'INT UNSIGNED NOT NULL')
            ->addColumn('price', 'DECIMAL(10,2) NOT NULL DEFAULT 0.00')
            ->addColumn('created_at', 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP')
            ->addColumn('updated_at', 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')
            ->addPrimary('id')
            ->addUnique('name')
            ->build();

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

    public function down(): void {
        $this->wpdb->query("DROP TABLE IF EXISTS {$this->table}");
    }
}

Step 4: Migration Manager

class MigrationManager {
    protected array $migrations = [];

    public function addMigration(MigrationInterface $migration): self {
        $this->migrations[] = $migration;
        return $this;
    }

    public function up(): void {
        foreach ($this->migrations as $migration) {
            $migration->up();
        }
    }

    public function down(): void {
        foreach ($this->migrations as $migration) {
            $migration->down();
        }
    }
}

Step 5: Hook into Plugin Activation

register_activation_hook(__FILE__, function () {
    global $wpdb;
    $charsetCollate = $wpdb->get_charset_collate();
    $prefix = $wpdb->prefix . 'rb_';

    $manager = new MigrationManager();
    $manager->addMigration(new CreateServicesTable($wpdb, $prefix, $charsetCollate));
    // Add other tables similarly
    $manager->up();
});

---

5. Benefits

• 💡 Easy Maintenance: Small files, focused changes.
• 🚀 Performance: Custom tables, no CPT overhead.
• 🧪 Testable: Each class testable in isolation.
• 🧩 Extensible: Add new tables without touching core logic.

---

6. Future Enhancements

• Track migration versions with a migrations table
• Add rollback CLI support
• Unit tests with WP_UnitTestCase
• Automate from JSON schema

---

7. Conclusion

You now have a production-level, dynamic, scalable table management system for Radius Booking — ready for growth, customization, and teamwork.