Skip to content
Skills Library
Programming · Planning

database-schema

/database-schema

Designs normalized, constrained, migration-friendly relational schemas - entity modeling, key and type selection, indexes derived from real query patterns, and safe forward/rollback migrations. Use when someone asks "design a schema for X", "should I normalize or denormalize this", "what should my primary key be", "how do I add this column without downtime", or "why is my unique constraint broken with soft deletes". Do NOT use for tuning a slow query on an existing schema - use sql-query-optimizer instead; for picking indexes from a live workload, use index-advisor; for sizing database connect

Repository
skills ↗
Licence
MIT
Last repository change
13.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 13.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Designs normalized, constrained, migration-friendly relational schemas - entity modeling, key and type selection, indexes derived from real query patterns, and safe forward/rollback migrations. Use when someone asks "design a schema for X", "should I normalize or denormalize this", "what should my primary key be", "how do I add this column without downtime", or "why is my unique constraint broken with soft deletes". Do NOT use for tuning a slow query on an existing schema - use sql-query-optimizer instead; for picking indexes from a live workload, use index-advisor; for sizing database connect

Triggers from the description

design a schema for Xshould I normalize or denormalize thiswhat should my primary key behow do I add this column without downtimewhy is my unique constraint broken with soft deletes
Original author description

Designs normalized, constrained, migration-friendly relational schemas - entity modeling, key and type selection, indexes derived from real query patterns, and safe forward/rollback migrations. Use when someone asks "design a schema for X", "should I normalize or denormalize this", "what should my primary key be", "how do I add this column without downtime", or "why is my unique constraint broken with soft deletes". Do NOT use for tuning a slow query on an existing schema - use sql-query-optimizer instead; for picking indexes from a live workload, use index-advisor; for sizing database connect

How it works

  1. Operating procedure
  2. Worked contrast: the schema and the migration it costs you
  3. Deliverable
  4. Do NOT
  5. Quality bar
  6. Escalation
Original SKILL.md headings · 116 lines, 1,231 words
SKILL.md preview
---
name: database-schema
description: Designs normalized, constrained, migration-friendly relational schemas - entity modeling, key and type selection, indexes derived from real query patterns, and safe forward/rollback migrations. Use when someone asks "design a schema for X", "should I normalize or denormalize this", "what should my primary key be", "how do I add this column without downtime", or "why is my unique constraint broken with soft deletes". Do NOT use for tuning a slow query on an existing schema - use sql-query-optimizer instead; for picking indexes from a live workload, use index-advisor; for sizing database connection pools, use connection-pool-tuner; for range/hash partitioning decisions, use partition-planner.
metadata:
  title: "Database Schema Designer"
---

# Database Schema Designer

A schema is the one part of a system you cannot refactor with a find-and-replace: every shortcut taken at design time becomes a locked-table migration on a hot production database later. This skill produces schemas that stay correct and fast as data grows - invalid states unrepresentable, indexes matched to real queries, and every change shippable without downtime.

## Operating procedure

Follow the steps in order. Constraints come before indexes because constraints define correctness and indexes only define speed; migrations come last because they depend on both.

### Step 1: Gather inputs

Collect these before writing any DDL. If a number is a guess, label it a guess and proceed.

1. Entities and relationships - nouns of the domain and their cardinality (1:1, 1:N, M:N).
2. The top 5-10 queries by expected frequency, with their filter and sort columns. Indexes come from this list, not from intuition.
3. Expected row counts at 1 year and 3 years per table, and read:write ratio per hot table. Default assumption if unknown: 10:1 read-heavy.
4. Multi-tenancy - single tenant, or `tenant_id` on every row?
5. Deletion semantics - hard delete, soft delete, or audit-retained?

### Step 2: Model, normalize, then denormalize only on evidence

Normalize to third normal form (3NF) by default: every non-key column depends on the key, the whole key, and nothing but the key. Denormalize a specific read path only when ALL of these hold:

- The path is measured hot (p95 latency above target, or it dominates query volume), not predicted hot.
- The normalized query joins 3+ tables or aggregates on every read.
- The duplicated data has a read:write ratio of at least 10:1 - copies of frequently-written data rot.
- You name the sync mechanism (trigger, transactional write-through in the app, or materialized view with a refresh cadence) in the same design doc as the copy. A denormalized column with no owner is a data-corruption bug on a timer.

For M:N relationships, use a join table with a composite primary key on both foreign keys.

### Step 3: Choose keys and types

- Primary keys: `BIGINT GENERATED ALWAYS AS IDENTITY`, or UUIDv7 when IDs are generated client-side or must not be guessable. Never UUIDv4 as a clustered/primary key - random inserts fragment the index and wreck insert throughput on large tables.
- Time: `TIMESTAMPTZ`, never naive local time.
- Money: `NUMERIC(19,4)` or integer minor units (`total_cents BIGINT`), never float.
- Fixed value sets: a `CHECK (col IN (...))` constraint, a Postgres enum, or a lookup table. Lookup table when values change at runtime; CHECK/enum when they change with a deploy.
- Use the narrowest correct type; width costs cache and index size on every row forever.

### Step 4: Make invalid states unrepresentable

Add `NOT NULL` by default, foreign keys with an explicit `ON DELETE` behavior (`RESTRICT` unless cascade is a designed feature), and `CHECK` constraints for domain rules (`total_cents >= 0`). Every constraint you skip becomes application code that at least one code path forgets.

### Step 5: Index from the query list

- Index every foreign key you join or filter on.
- Composite index column order: equality-filtered columns first, then range/sort columns. `(user_id, created_at DESC)` serves "this user's recent orders"; the reverse order does not.
- Covering index (`INCLUDE` selected columns) when a top query can be served from the index alone.
- Partial index for skewed predicates: `WHERE status = 'pending'` when pending is under ~5% of rows but queried constantly.
- Every index taxes every write. After launch, drop unused indexes - check `pg_stat_user_indexes` for zero-scan indexes.
- Multi-tenant: `tenant_id` leads every index prefix and appears in every unique key.

### Step 6: Write migrations that ship without downtime

- One logical change per migration, always with a rollback.
- Adding a NOT NULL column to a large table is three steps: add nullable, backfill in batches of 1,000-10,000 rows (larger batches hold locks and bloat WAL), then add the constraint (`NOT VALID` then `VALIDATE CONSTRAINT` on Postgres to avoid a full-table lock).
- `CREATE INDEX CONCURRENTLY` - a plain CREATE INDEX blocks writes for the whole build.
- Never rename-and-reuse a column in the same deploy as the code reading it. Split into two deploys: add new + dual-write, then drop old.
- Soft deletes: `deleted_at TIMESTAMPTZ`, and every unique constraint becomes a partial unique index `WHERE deleted_at IS NULL` - otherwise a deleted row blocks re-creation forever.

## Worked contrast: the schema and the migration it costs you

Bad - three common shortcuts:

```sql
CREATE TABLE orders (
  id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,  -- UUIDv4: fragments the index
  user_email TEXT,                                        -- denormalized copy, no sync owner
  status     TEXT,                                        -- free text: 'Paid', 'paid', 'PAID '
  total      FLOAT                                        -- float money: rounding drift
);
```

Migration bill this schema generates within a year: a data-cleaning pass to canonicalize every status typo before a CHECK constraint can be validated; a backfill joining users to repair stale `user_email` copies; a `FLOAT -> NUMERIC` type change that rewrites the whole table under lock; and insert throughput decay from UUIDv4 fragmentation that no migration fixes without a new PK.

Good - the same table designed forward:

```sql
CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status      TEXT NOT NULL CHECK (status IN ('pending','paid','shipped')),
  total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
```

Future migrations against this table are additive: new columns arrive nullable-then-backfilled, new statuses extend the CHECK, and nothing requires a cleanup pass first.

## Deliverable

Produce a schema design containing: the DDL for every table with constraints inline; the index list annotated with the specific query each index serves; the denormalization decisions (if any) each naming its sync mechanism; and the migration plan for getting from the current state to the target, ordered and reversible.

## Do NOT

- Do not denormalize preemptively "for performance" - you inherit a sync bug without evidence of a speed problem.
- Do not index every column "to be safe"; each index slows every write and most never get scanned.
- Do not use UUIDv4 as a primary key on high-insert tables; use BIGINT identity or UUIDv7.
- Do not store money in floats or timestamps without timezone - both corrupt silently.
- Do not run a plain `CREATE INDEX` or an immediate `SET NOT NULL` on a large production table; both take locks that stall writes.
- Do not forget `deleted_at` in unique constraints when soft-deleting - the second "delete and recreate" fails in production.

## Quality bar

A finished design passes only if:

- Every index maps to a named query from Step 1; zero speculative indexes.
- Every table has a primary key, and every foreign key has an explicit ON DELETE behavior.
- No float money, no naive timestamps, no free-text enums.
- Every denormalized copy names its sync mechanism and owner.
- Every migration in the plan is reversible and lock-safe on the stated table sizes.

## Escalation

For query plans that stay slow on a sound schema, hand off to explain-plan-reader or sql-query-optimizer. When a table's growth projection crosses ~100M rows or needs time-based pruning, bring in partition-planner before it gets there. Gate risky production migrations through migration-safety-checker.

Versions

RepositoryScoreLicence
This page · SkillMedev/skills100MIT
tlkppm/ModelBridge100MIT
jr2804/prompts100MIT

Database snapshot: 13.11.2026 · package version f88dd3296279 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 90database-design
    Picks a database and ORM, designs schemas and indexes, plans safe migrations. Use when designing a schema, adding indexes or planning a migration.
  • 100database-schema-designer
    Designs relational and document database schemas from requirements or an existing app - tables and collections, keys, relationships, constraints, indexes driven by real query patterns, ERDs, and reversible migration scripts - with PostgreSQL (including Supabase) as the default…
  • 98database-design-patterns
    Schema design, normalization decisions, indexing strategies, and migration safety for relational databases. Use when designing tables, choosing between normalize vs denormalize, selecting index types, planning zero-downtime migrations, or setting up connection pooling. Activate…
  • 100database-engineering
    Design, query, migrate, secure, and optimize relational databases, especially PostgreSQL. Use for schema design, SQL, indexes, transactions, concurrency, data integrity, backup/recovery, and production migrations.
  • 100database-engineering
    End-to-end database engineering workflow for designing relational data models, writing schemas and migrations, reviewing constraints, creating indexes, optimizing queries, and hardening persistence behavior. Use when the user asks to design a database, create or change tables,…
  • 100database-schema-design
    Design normalized database schemas with tables, relationships, indexes, and constraints for any application domain. Use when the user requests database schema design or provides relevant inputs for this workflow.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.