# Database Schema Designer

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

- Skill: `fadil369/database-schema-designer` (Agent Skill)
- Install (CLI): `npx skillmds@latest add fadil369/database-schema-designer`
- Raw SKILL.md: https://api.skillmd.com/api/skills/fadil369/database-schema-designer/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: fadil369 (https://skillmd.com/u/fadil369)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/fadil369/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
```sql
-- 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
```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
```javascript
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
| Query Pattern | Index Type |
|---|---|
| Exact match | B-tree |
| Range queries | B-tree |
| Full text search | GIN / Full-text |
| Array contains | GIN |
| Geospatial | GiST |

## Integration
- Works with: data-migration-orchestrator, multi-tenant-architecture