Database Schema Designer

Database Schema Designer

Designs normalized, performant database schemas with proper indexing, constraints, and migration strategies. Supports PostgreSQL, MySQL, and SQLite.

Decision Framework

When to Apply

Use when: New database design, schema migrations, performance optimization, relationship modeling

When NOT to Apply

Don't use when: Simple key-value stores, pure document DB design

Anti-Patterns

1. No Indexes on Foreign Keys

-- BAD: No index on foreign key
CREATE TABLE orders (id SERIAL, user_id INT REFERENCES users(id));

-- GOOD: Indexed foreign key
CREATE TABLE orders (id SERIAL, user_id INT REFERENCES users(id));
CREATE INDEX idx_orders_user_id ON orders(user_id);

2. Storing JSON Blobs in SQL

-- BAD: Unqueryable JSON dump
CREATE TABLE users (id SERIAL, data JSONB);

-- GOOD: Indexed JSONB with GIN index
CREATE TABLE users (id SERIAL, profile JSONB);
CREATE INDEX idx_users_profile ON users USING GIN (profile);

3. No Migrations

Applying schema changes manually leads to drift between environments.

Trigger Phrases

• "Design database"
• "Schema design"
• "Table structure"
• "Database migration"
• "Index optimization"
• "Relationship modeling"

Patterns

Migration System

class MigrationRunner {
  async run(migrations) {
    const applied = await this.getAppliedMigrations();
    for (const migration of migrations) {
      if (!applied.includes(migration.id)) {
        await this.db.transaction(async (trx) => {
          await migration.up(trx);
          await trx('migrations').insert({ id: migration.id, applied_at: new Date() });
        });
      }
    }
  }
}

Indexing Strategy

Integration

• Works with: data-migration-orchestrator, multi-tenant-architecture