Skip to content

skillDatabases

Database Schema Design

Models entities, keys, relationships and constraints for a new feature, and states the reason for any denormalization before writing DDL.

npx workspacetemplates add database-schema-design --tool claude-code

Writes 1 file to .claude/skills/database-schema-design/

.claude/skills/database-schema-design/SKILL.md29 lines · 1.8 KBFile
---
name: database-schema-design
description: Models entities, keys, relationships and constraints for a new feature, and states the reason for any denormalization before writing DDL.
---

# Database schema design

Start from what the application must ask of the data, not from the UI.

## Steps

1. Write the ten most important reads and writes in plain sentences ("orders for a customer, newest first"). The schema must make these cheap.
2. Name the entities and their relationships: one-to-many, many-to-many (needs a join table), or one-to-one (usually merge them unless the data is optional or large).
3. Choose primary keys. Use a surrogate key (bigint identity or UUID v7) and keep natural identifiers as separate unique columns. Never use an email or name as a key.
4. Pick types carefully: `numeric` for money (or integer cents), `timestamptz` for instants, text with a length check instead of unbounded text where a limit exists, enums via a lookup table or check constraint.
5. Add constraints in the schema: `NOT NULL` by default, foreign keys with an explicit `ON DELETE` choice, `UNIQUE` on every real uniqueness rule, `CHECK` for ranges and states.
6. Normalize to third normal form first. Denormalize only for a named, measured read problem, and then record the source of truth and how the copy stays in sync.
7. Index each foreign key and each query filter from step 1. Do not add speculative indexes.
8. Add `created_at`, `updated_at` and soft delete only where the product needs history.

## Rules

- No comma-separated lists or repeated columns (`tag1`, `tag2`).
- Every denormalized column gets a comment saying why.
- Write the migration as reversible, and check it runs on a copy of production data.

## Report

An ER summary, the DDL, the query-to-index mapping, and each trade-off with its reason.

What it does

A schema outlives the code around it, so mistakes in keys and constraints are the expensive ones. This skill makes the agent work from the questions the data must answer, then write the tables.

Constraints do the policing: foreign keys, not null, unique and check rules go in the database, where every writer has to obey them.

Example

you › Design tables for team workspaces with members, roles and invitations

agent › Five tables: workspaces, users, memberships (workspace_id, user_id, role check constraint, unique pair), invitations (token hash, expires_at, unique open invite per email and workspace) and audit_events. The member count on workspaces is denormalized, kept by a trigger, and noted as such.

Questions

UUID or integer keys?
Integers are smaller and faster to index. Use UUID v7 when ids are created in several places or exposed in URLs. Avoid random UUID v4 for large clustered tables.
When is JSON in a column acceptable?
For data you store and return whole and never filter or join on. If you query inside it regularly, it wants its own columns.
  • Database Connection Pooling

    SkillDatabases

    Free
  • Database Migration Safety

    SkillDatabases

    Free
  • MongoDB Schema Modeling

    Skillmongodb

    Free
  • MySQL Query Tuning

    Skillmysql

    Free