Compressing natural language only, preserving all code blocks, headings, URLs, and technical terms exactly.

# Database Schema Skill

> **Purpose:** DB table design, migrations, schema management
> **When to use:** Any DB schema change or table creation
> **Related skills:** wordpress-plugin-foundation-skill.md (always applies)

---

## Quick Reference

```php
// Create table with dbDelta
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE {$wpdb->prefix}presszone_forum_posts (
    post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    thread_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    content LONGTEXT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME DEFAULT NULL,
    is_deleted TINYINT(1) DEFAULT 0,
    
    INDEX idx_thread_id (thread_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
) $charset_collate;";

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

---

## Table Creation with dbDelta

### Basic Structure

```php
public function createTables(): void
{
    global $wpdb;
    
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'presszone_forum_posts';
    
    $sql = "CREATE TABLE $table_name (
        post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        thread_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NOT NULL,
        content LONGTEXT NOT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME DEFAULT NULL,
        
        INDEX idx_thread_id (thread_id),
        INDEX idx_user_id (user_id)
    ) $charset_collate;";
    
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}
```

### dbDelta Requirements (CRITICAL)

```php
// CORRECT - dbDelta compatible
$sql = "CREATE TABLE {$wpdb->prefix}table_name (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    created_at DATETIME NOT NULL,
    
    INDEX idx_name (name)
) $charset_collate;";

// RULES for dbDelta:
// 1. Two spaces after PRIMARY KEY
// 2. No spaces around DEFAULT values
// 3. Column definitions on separate lines
// 4. Indexes after column definitions
// 5. Must use $charset_collate
```

---

## Column Types

### Integer Types

```sql
TINYINT       -- -128 to 127 (or 0 to 255 UNSIGNED)
SMALLINT      -- -32768 to 32767
MEDIUMINT     -- -8388608 to 8388607
INT           -- -2147483648 to 2147483647
BIGINT        -- Very large numbers

-- WordPress standard for IDs
BIGINT UNSIGNED  -- 0 to 18446744073709551615
```

### String Types

```sql
VARCHAR(255)   -- Variable length, max 255 chars
TEXT           -- Up to 65,535 chars
MEDIUMTEXT     -- Up to 16,777,215 chars
LONGTEXT       -- Up to 4,294,967,295 chars
CHAR(10)       -- Fixed length
```

### Date/Time Types

```sql
DATETIME       -- '2025-01-15 14:30:00'
DATE           -- '2025-01-15'
TIME           -- '14:30:00'
TIMESTAMP      -- Auto-updates on row change
```

### Boolean

```sql
TINYINT(1)     -- 0 or 1 (WordPress standard for boolean)
```

---

## Indexes

### Types of Indexes

```sql
-- Primary Key (unique, not null)
PRIMARY KEY (post_id)

-- Unique Index
UNIQUE KEY uk_slug (slug)

-- Regular Index
INDEX idx_thread_id (thread_id)
INDEX idx_user_id (user_id)

-- Composite Index
INDEX idx_thread_user (thread_id, user_id)

-- Full-Text Index
FULLTEXT KEY ft_content (content)
```

### When to Add Indexes

```php
// Add indexes for:
// 1. Foreign keys
INDEX idx_thread_id (thread_id)

// 2. Frequently queried columns
INDEX idx_created_at (created_at)

// 3. WHERE clause columns
INDEX idx_status (status)

// 4. ORDER BY columns
INDEX idx_last_post_date (last_post_date)

// 5. Composite queries
INDEX idx_thread_status (thread_id, status)
```

---

## Common Table Patterns

### Posts/Content Table

```php
$sql = "CREATE TABLE {$wpdb->prefix}presszone_forum_posts (
    post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    thread_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED DEFAULT 0,
    content LONGTEXT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME DEFAULT NULL,
    is_deleted TINYINT(1) DEFAULT 0,
    deleted_at DATETIME DEFAULT NULL,
    deleted_by BIGINT UNSIGNED DEFAULT NULL,
    
    INDEX idx_thread_id (thread_id),
    INDEX idx_user_id (user_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_created_at (created_at),
    INDEX idx_deleted (is_deleted)
) $charset_collate;";
```

### Relationship Table (Many-to-Many)

```php
$sql = "CREATE TABLE {$wpdb->prefix}presszone_forum_reactions (
    reaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    content_type VARCHAR(20) NOT NULL,
    content_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reaction_type VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    
    UNIQUE KEY uk_user_content (content_type, content_id, user_id),
    INDEX idx_content (content_type, content_id),
    INDEX idx_user_id (user_id)
) $charset_collate;";
```

### Metadata Table

```php
$sql = "CREATE TABLE {$wpdb->prefix}presszone_forum_postmeta (
    meta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value LONGTEXT,
    
    INDEX idx_post_id (post_id),
    INDEX idx_meta_key (meta_key)
) $charset_collate;";
```

---

## Soft Deletes

### Soft Delete Pattern

```php
// Add soft delete columns
is_deleted TINYINT(1) DEFAULT 0,
deleted_at DATETIME DEFAULT NULL,
deleted_by BIGINT UNSIGNED DEFAULT NULL,

INDEX idx_deleted (is_deleted)

// Soft delete query
$wpdb->update(
    $wpdb->prefix . 'presszone_forum_posts',
    [
        'is_deleted' => 1,
        'deleted_at' => current_time('mysql'),
        'deleted_by' => get_current_user_id(),
    ],
    ['post_id' => $post_id],
    ['%d', '%s', '%d'],
    ['%d']
);

// Query excluding deleted
$posts = $wpdb->get_results(
    "SELECT * FROM {$wpdb->prefix}presszone_forum_posts 
     WHERE is_deleted = 0"
);
```

---

## Nested Set Model (Hierarchical Data)

### Tree Structure Table

```php
$sql = "CREATE TABLE {$wpdb->prefix}presszone_forum_nodes (
    node_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED DEFAULT 0,
    lft INT UNSIGNED NOT NULL,
    rgt INT UNSIGNED NOT NULL,
    depth INT UNSIGNED DEFAULT 0,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL,
    
    UNIQUE KEY uk_slug (slug),
    INDEX idx_lft_rgt (lft, rgt),
    INDEX idx_parent_id (parent_id)
) $charset_collate;";

// Query all descendants
$descendants = $wpdb->get_results($wpdb->prepare(
    "SELECT child.* 
     FROM {$wpdb->prefix}presszone_forum_nodes child
     INNER JOIN {$wpdb->prefix}presszone_forum_nodes parent 
         ON child.lft BETWEEN parent.lft AND parent.rgt
     WHERE parent.node_id = %d
     ORDER BY child.lft",
    $node_id
));

// Query breadcrumb path
$path = $wpdb->get_results($wpdb->prepare(
    "SELECT parent.* 
     FROM {$wpdb->prefix}presszone_forum_nodes child
     INNER JOIN {$wpdb->prefix}presszone_forum_nodes parent 
         ON child.lft BETWEEN parent.lft AND parent.rgt
     WHERE child.node_id = %d
     ORDER BY parent.lft",
    $node_id
));
```

---

## Migrations

### Version-Based Migrations

```php
class Migrations
{
    private const CURRENT_VERSION = '1.2.0';
    
    public function run(): void
    {
        $installed_version = get_option('presszone_forum_db_version', '0.0.0');
        
        if (version_compare($installed_version, '1.0.0', '<')) {
            $this->migrate_1_0_0();
        }
        
        if (version_compare($installed_version, '1.1.0', '<')) {
            $this->migrate_1_1_0();
        }
        
        if (version_compare($installed_version, '1.2.0', '<')) {
            $this->migrate_1_2_0();
        }
        
        update_option('presszone_forum_db_version', self::CURRENT_VERSION);
    }
    
    private function migrate_1_0_0(): void
    {
        // Initial schema
        $this->createTables();
    }
    
    private function migrate_1_1_0(): void
    {
        global $wpdb;
        
        // Add new column
        $wpdb->query(
            "ALTER TABLE {$wpdb->prefix}presszone_forum_posts 
             ADD COLUMN reaction_score INT DEFAULT 0 AFTER likes_count"
        );
        
        // Add index
        $wpdb->query(
            "ALTER TABLE {$wpdb->prefix}presszone_forum_posts 
             ADD INDEX idx_reaction_score (reaction_score)"
        );
    }
    
    private function migrate_1_2_0(): void
    {
        // Data migration
        $this->migrateOldDataToNewFormat();
    }
}
```

---

## Foreign Keys (Use with Caution)

### Adding Foreign Keys

```php
// WordPress doesn't use foreign keys by default
// But you can add them if needed

$wpdb->query(
    "ALTER TABLE {$wpdb->prefix}presszone_forum_posts
     ADD CONSTRAINT fk_thread_id
     FOREIGN KEY (thread_id) 
     REFERENCES {$wpdb->prefix}presszone_forum_threads(thread_id)
     ON DELETE CASCADE"
);

// Note: InnoDB engine required for foreign keys
```

---

## Table Optimization

### Analyze and Optimize

```php
public function optimizeTables(): void
{
    global $wpdb;
    
    $tables = [
        $wpdb->prefix . 'presszone_forum_posts',
        $wpdb->prefix . 'presszone_forum_threads',
        $wpdb->prefix . 'presszone_forum_nodes',
    ];
    
    foreach ($tables as $table) {
        $wpdb->query("OPTIMIZE TABLE $table");
        $wpdb->query("ANALYZE TABLE $table");
    }
}
```

---

## Data Types Best Practices

### Choosing Column Types

```php
// User IDs - match WordPress users table
user_id BIGINT UNSIGNED NOT NULL

// Counts/Numbers
post_count INT UNSIGNED DEFAULT 0
views BIGINT UNSIGNED DEFAULT 0

// Short text
title VARCHAR(200) NOT NULL
slug VARCHAR(200) NOT NULL
status VARCHAR(20) DEFAULT 'active'

// Long text
content LONGTEXT NOT NULL
description TEXT

// Dates
created_at DATETIME NOT NULL
updated_at DATETIME DEFAULT NULL

// Boolean
is_active TINYINT(1) DEFAULT 1
is_deleted TINYINT(1) DEFAULT 0

// Decimal numbers
rating DECIMAL(3,2) DEFAULT 0.00  -- 0.00 to 9.99
```

---

## Default Values

### Setting Defaults

```php
// Numeric defaults
post_count INT UNSIGNED DEFAULT 0
views BIGINT UNSIGNED DEFAULT 0

// String defaults
status VARCHAR(20) DEFAULT 'draft'

// Date defaults
created_at DATETIME NOT NULL  -- Set in application
updated_at DATETIME DEFAULT NULL

// Boolean defaults
is_active TINYINT(1) DEFAULT 1
```

---

## Charset and Collation

### WordPress Standard

```php
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();

// Returns something like:
// DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

// Always use this in CREATE TABLE
$sql = "CREATE TABLE {$wpdb->prefix}table_name (
    ...
) $charset_collate;";
```

---

## Table Naming

### Naming Conventions

```php
// WordPress prefix + plugin prefix + table name
$wpdb->prefix . 'presszone_forum_posts'
$wpdb->prefix . 'presszone_forum_threads'
$wpdb->prefix . 'presszone_forum_nodes'

// Singular vs Plural
// Use plural for collection tables
'presszone_forum_posts'      // Collection of posts
'presszone_forum_users'      // Collection of users

// Use singular for relationship tables
'presszone_forum_postmeta'   // Metadata for posts
'presszone_forum_usermeta'   // Metadata for users
```

---

## Checking Table Existence

```php
public function tableExists(string $tableName): bool
{
    global $wpdb;
    
    $table = $wpdb->get_var($wpdb->prepare(
        "SHOW TABLES LIKE %s",
        $tableName
    ));
    
    return $table === $tableName;
}

// Usage
if (!$this->tableExists($wpdb->prefix . 'presszone_forum_posts')) {
    $this->createTables();
}
```

---

## Dropping Tables

### Safe Table Deletion

```php
public function dropTables(): void
{
    global $wpdb;
    
    $tables = [
        $wpdb->prefix . 'presszone_forum_posts',
        $wpdb->prefix . 'presszone_forum_threads',
        $wpdb->prefix . 'presszone_forum_nodes',
    ];
    
    foreach ($tables as $table) {
        $wpdb->query("DROP TABLE IF EXISTS $table");
    }
}

// Call on plugin uninstall (not deactivation)
register_uninstall_hook(__FILE__, ['PluginClass', 'dropTables']);
```

---

## Common Mistakes

| Mistake | Fix |
|---------|-----|
| Not using `$charset_collate` | Always include in CREATE TABLE |
| Wrong dbDelta syntax | Follow exact spacing rules |
| Missing indexes on foreign keys | Add INDEX for all foreign key columns |
| Using INT for user IDs | Use BIGINT UNSIGNED to match WordPress |
| Not setting DEFAULT values | Set sensible defaults for all columns |
| Using TIMESTAMP instead of DATETIME | Use DATETIME for consistency |
| Missing soft delete columns | Add is_deleted, deleted_at, deleted_by |
| Not versioning migrations | Track schema version in options |
| Dropping tables on deactivation | Only drop on uninstall |
| Hardcoding table prefix | Always use `$wpdb->prefix` |

---

## Schema Documentation

### Document Your Schema

```php
/**
 * Posts Table Schema
 * 
 * Stores forum posts with nested reply support
 * 
 * Columns:
 * - post_id: Primary key
 * - thread_id: Foreign key to threads table
 * - user_id: Foreign key to WordPress users
 * - parent_id: Self-referencing for nested replies (0 = top-level)
 * - content: Post content (HTML allowed)
 * - created_at: Post creation timestamp
 * - updated_at: Last edit timestamp (NULL if never edited)
 * - is_deleted: Soft delete flag
 * - deleted_at: Deletion timestamp
 * - deleted_by: User ID who deleted
 * 
 * Indexes:
 * - idx_thread_id: For querying posts by thread
 * - idx_user_id: For querying posts by user
 * - idx_parent_id: For querying nested replies
 * - idx_created_at: For sorting by date
 * - idx_deleted: For filtering deleted posts
 */
```

---

## Integration with Other Skills

- **wordpress-plugin-foundation-skill.md** - Security + compliance (always applies)
- **php-skill.md** - PHP patterns for migrations
- **sql-skill.md** - Query patterns for schema
- **caching-skill.md** - Cache invalidation on schema changes