Column and Index Factories

PHPNomad's database package provides pre-built factories for common column and index patterns. These factories eliminate repetitive schema definitions and ensure consistency across tables. Instead of manually defining columns with Column every time, you use specialized factories that encode best practices and standard patterns.

Factories are building blocks you compose in your table definitions. Each factory produces properly configured column or index objects with sensible defaults, while still allowing customization when needed.

Why Factories Exist

Without factories, every table would repeat the same patterns:

// Repetitive: defining a primary key in every table
$this->columnFactory->int('id', 11)->autoIncrement()->notNull();

// Repetitive: defining timestamps in every table
$this->columnFactory->datetime('created_at')->default('CURRENT_TIMESTAMP')->notNull();
$this->columnFactory->datetime('updated_at')
    ->default('CURRENT_TIMESTAMP')
    ->onUpdate('CURRENT_TIMESTAMP')
    ->notNull();

With factories, these become:

$this->primaryKeyFactory->create('id');
$this->dateCreatedFactory->create('created_at');
$this->dateModifiedFactory->create('updated_at');

This reduces duplication, prevents typos, and makes table definitions scannable.

Column Factories

PHPNomad provides four specialized column factories for the most common patterns.

PrimaryKeyFactory

Creates auto-increment integer primary keys—the standard pattern for identifying rows.

What it creates:

Usage:

final class PostTable implements Table
{
    public function __construct(
        private Column $columnFactory,
        private PrimaryKeyFactory $primaryKeyFactory
    ) {}

    public function getColumns(): array
    {
        return [
            $this->primaryKeyFactory->create('id'),
            // other columns...
        ];
    }

    public function getPrimaryKey(): PrimaryKey
    {
        return $this->primaryKeyFactory->create('id');
    }
}

Customization:

// Use BIGINT for very large tables
$this->primaryKeyFactory->create('id', size: 'big');

DateCreatedFactory

Creates timestamp columns that automatically capture when a row was created.

What it creates:

Usage:

public function getColumns(): array
{
    return [
        $this->primaryKeyFactory->create('id'),
        $this->columnFactory->string('title', 255)->notNull(),
        $this->dateCreatedFactory->create('created_at'),
    ];
}

This column is set once when the row is inserted and never changes.

When to use:

DateModifiedFactory

Creates timestamp columns that automatically update whenever a row changes.

What it creates:

Usage:

public function getColumns(): array
{
    return [
        $this->primaryKeyFactory->create('id'),
        $this->columnFactory->string('title', 255)->notNull(),
        $this->dateCreatedFactory->create('created_at'),
        $this->dateModifiedFactory->create('updated_at'),
    ];
}

This column is updated automatically by the database every time the row is modified.

When to use:

ForeignKeyFactory

Creates foreign key columns that reference primary keys in other tables.

What it creates:

Usage:

public function __construct(
    private Column $columnFactory,
    private ForeignKeyFactory $foreignKeyFactory
) {}

public function getColumns(): array
{
    return [
        $this->primaryKeyFactory->create('id'),
        $this->columnFactory->string('title', 255)->notNull(),
        
        // Reference the 'id' column in the 'authors' table
        $this->foreignKeyFactory->create('author_id', 'authors', 'id'),
        
        $this->dateCreatedFactory->create('created_at'),
    ];
}

Customization:

// Allow NULL (optional foreign key)
$this->foreignKeyFactory->create('author_id', 'authors', 'id', nullable: true);

// Cascade deletes (delete posts when author is deleted)
$this->foreignKeyFactory->create('author_id', 'authors', 'id', onDelete: 'CASCADE');

// Set NULL on delete (orphan posts when author is deleted)
$this->foreignKeyFactory->create('author_id', 'authors', 'id', onDelete: 'SET NULL', nullable: true);

When to use:

Index Factory

Indexes improve query performance by allowing the database to find rows faster. The IndexFactory creates single-column and composite indexes.

IndexFactory::create()

Creates a single-column index.

Usage:

public function __construct(
    private IndexFactory $indexFactory
) {}

public function getIndexes(): array
{
    return [
        // Index on author_id for "find all posts by author" queries
        $this->indexFactory->create('idx_author', ['author_id']),
        
        // Index on published_date for sorting and range queries
        $this->indexFactory->create('idx_published', ['published_date']),
    ];
}

IndexFactory::composite()

Creates a composite index that spans multiple columns. These are useful for queries that filter on multiple fields at once.

Usage:

public function getIndexes(): array
{
    return [
        // Composite index for "posts by author, sorted by publish date"
        $this->indexFactory->composite('idx_author_date', ['author_id', 'published_date']),
    ];
}

When to use composite indexes:

Note: Column order matters. The index ['author_id', 'published_date'] can serve:

But not:

Real-World Example: Full Table with Factories

Here's a complete table that uses all the factories:

<?php

use PHPNomad\Database\Interfaces\Table;
use PHPNomad\Database\Factories\Column;
use PHPNomad\Database\Factories\PrimaryKey;
use PHPNomad\Database\Factories\ForeignKey;
use PHPNomad\Database\Factories\DateCreated;
use PHPNomad\Database\Factories\DateModified;
use PHPNomad\Database\Factories\Index;

final class PostTable implements Table
{
    public function __construct(
        private Column $columnFactory,
        private PrimaryKeyFactory $primaryKeyFactory,
        private ForeignKeyFactory $foreignKeyFactory,
        private DateCreatedFactory $dateCreatedFactory,
        private DateModifiedFactory $dateModifiedFactory,
        private IndexFactory $indexFactory
    ) {}

    public function getTableName(): string
    {
        return 'posts';
    }

    public function getColumns(): array
    {
        return [
            // Auto-increment primary key
            $this->primaryKeyFactory->create('id'),
            
            // Regular columns
            $this->columnFactory->string('title', 255)->notNull(),
            $this->columnFactory->text('content')->notNull(),
            $this->columnFactory->string('slug', 255)->notNull()->unique(),
            $this->columnFactory->datetime('published_date')->nullable(),
            
            // Foreign key to authors table
            $this->foreignKeyFactory->create('author_id', 'authors', 'id'),
            
            // Timestamps
            $this->dateCreatedFactory->create('created_at'),
            $this->dateModifiedFactory->create('updated_at'),
        ];
    }

    public function getPrimaryKey(): PrimaryKey
    {
        return $this->primaryKeyFactory->create('id');
    }

    public function getIndexes(): array
    {
        return [
            // Single-column indexes
            $this->indexFactory->create('idx_author', ['author_id']),
            $this->indexFactory->create('idx_published', ['published_date']),
            $this->indexFactory->create('idx_slug', ['slug']), // unique already indexed, but explicit
            
            // Composite index for "author's published posts, sorted by date"
            $this->indexFactory->composite('idx_author_published', [
                'author_id',
                'published_date'
            ]),
        ];
    }
}

This table uses factories for:

The result is a clean, readable schema definition with minimal boilerplate.

Best Practices

When using factories:

When NOT to Use Factories

Factories are for common patterns. For unique or domain-specific columns, use the base Column factory:

// Custom columns that don't fit factory patterns
$this->columnFactory->decimal('price', 10, 2)->notNull(),
$this->columnFactory->json('metadata')->nullable(),
$this->columnFactory->enum('status', ['draft', 'published', 'archived'])->default('draft'),

Factories reduce boilerplate for 90% of columns. The remaining 10% are domain-specific and should use Column directly.

What's Next

To see how these factories are used in context, see: