Database Schema Designer

Demo

Click to play with sound.

---
name: Database Schema Designer
description: Design normalized, indexed, migration-friendly relational schemas.
---

# Database Schema Designer

Design schemas that stay correct and fast as data grows.

## Process

1. Model the entities and relationships before touching DDL.
2. Normalize to 3NF, then denormalize deliberately for proven read hotspots.
3. Choose primary keys; pick indexes from real query patterns.
4. Add constraints to make invalid states unrepresentable.
5. Write forward and rollback migrations.

## Keys and types

- Prefer `BIGINT` identity or UUIDv7 for primary keys. Avoid UUIDv4 as a clustered key — random inserts fragment the index.
- Use the narrowest correct type. `TIMESTAMPTZ` for time, never naive local time.
- Store money as `NUMERIC(19,4)`, never float.
- Use enums or a lookup table for fixed value sets.

## Constraints… install to load the full skill