Designing Database Schema

Your Schema is a Moving Target

You're writing migrations in your head. You define a users table, slap an id INT AUTO_INCREMENT on it, and start coding endpoints. Three weeks later, the product team asks for multi-tenancy, and you realize every query needs a tenant_id. You add the column. Now you have to migrate 4 million rows during peak traffic, and your ORM layer throws a fit because the seed data doesn't match the new constraint.

Or worse: you denormalize a customer_address into the orders table to save a join, and now you have to update six tables every time a user changes their zip code. You miss a code path. A user updates their profile, the invoice still shows the old address, and support gets a ticket.

Relational modeling isn't just about drawing boxes; it's about predicting access patterns before you write a single line of SQL. Most engineers skip the design phase because the pressure to ship is high. But a bad schema is technical debt that compounds daily. As Oracle documentation notes, third normal form modeling minimizes redundancy, but applying it without context leads to performance cliffs ^[6]. You need a workflow that balances normalization with your actual query load, not a heuristic you remember from a college textbook.

We built this skill so you don't have to. The Designing Database Schema skill gives you a structured, 5-phase workflow that forces you to think about requirements, normalization, and validation before you touch the database. It references production-grade templates, validators, and PostgreSQL best practices so you can ship schemas that hold up under load.

The Hidden Cost of Schema Drift

Every missing index on a foreign key is a table scan waiting to happen. When your P99 latency jumps from 50ms to 800ms because a migration added a non-indexed column that broke your query plan, product notices. You lose trust. You spend the next sprint firefighting slow queries instead of shipping features.

The cost isn't just latency. It's schema evolution. Without a structured approach, you end up with "schema drift" where your ORM models no longer match the database. You spend hours debugging why a join fails in production but works locally. You introduce insertion, update, and deletion anomalies because you didn't check for transitive dependencies.

"Only experienced database designers should denormalize," warns Oracle Magazine, noting that increasing redundancy might marginally improve query performance but always increases complexity and risk ^[5]. If your team doesn't have a senior DBA on call, you're flying blind. Blind denormalization leads to data inconsistency. Blind normalization leads to N+1 query storms.

If you're also thinking about how this data flows into your analytics layer, you'll want to check out the Database Design Pack to ensure your operational schema supports your analytical needs. And if you're building a data warehouse, the Data Warehouse Pack covers star schema patterns and slowly changing dimensions that complement your relational design.

A SaaS Team's Denormalization Trap

Imagine a team shipping a multi-tenant SaaS platform. They start with a clean 3NF design: User, Subscription, Invoice. It works fine for 10k users. Then the product team asks for a "Quick View" dashboard that aggregates invoice totals, subscription status, and user email in a single API call.

The engineer's first instinct: Add a computed column. Bad idea. Updates become slow. The second instinct: Denormalize. Copy user_email to Invoice. Now they have to update Invoice on every user profile change. They miss a code path. A user changes their email, the invoice still shows the old one. Support ticket.

"I normalize all data for OLTP -- I never store redundant data," says Ask TOM, but blindly following that without considering access patterns can kill write throughput and create maintenance nightmares ^[7]. The team realizes they need a validation step. They try to manually check constraints. They miss a transitive dependency: ProductPrice depends on ProductID, which depends on OrderID. This is a classic violation ^[6].

The team also reviews design principles for NoSQL systems like DynamoDB, noting that single table design allows storing multiple types in one table, but that pattern requires a completely different mental model than relational design ^[4]. They need to stick to relational best practices for their OLTP workload ^[2].

"There are many normalization rules," notes Oracle Connect. "In my experience, many engineers struggle to know when to stop," ^[8]. The team installs a structured workflow. They define requirements first. They model relations. They validate for normalization violations. They implement with transactional migrations. They use UUID v7 for sortability. They add composite indexes. They catch the transitive dependency before migration. They ship. No table locks. No data inconsistency. No 3 AM on-call pings.

What Changes When You Lock the Workflow

Once you install this skill, your schema design stops being an art project and becomes a repeatable engineering process. You get a 5-phase workflow that forces you to think about requirements before you write SQL.

Your Prisma schemas come with UUID v7 primary keys out of the box, ensuring sortability without the collision risks of v4. Soft deletes are standard. Audit timestamps are automatic. You reference references/postgresql-best-practices.md for ENUM types, JSONB usage, and schema organization.

When you run scripts/validate-schema.sh, it catches naming violations and missing required fields before you commit. The validators/schema-lint.py parses your AST and enforces rules: all models have @id, no string primary keys without @db.Uuid. You get validators/normalization-checker.py that detects transitive dependencies and repeating groups. It exits 1 if violations are found, blocking bad migrations.

Your migrations are wrapped in transactions. Rollback comments are present. Indexes are optimized. You use references/indexing-strategies.md to decide between B-tree, GIN, and GiST. You know when not to index. You use references/prisma-relational-modeling.md for cascade/setNull actions and composite keys.

We include examples/ecommerce-schema.prisma so you can see 3NF in action with User, Product, Order models. We include examples/anti-patterns.md so you can see before/after examples of string PKs, missing indexes, and over-normalization. You scaffold projects in seconds with scripts/scaffold-project.sh. You validate tests with tests/schema-validation.test.sh.

This reduces schema-related incidents to near zero. Your team ships faster because they aren't debating table structures in PRs. You sleep at night.

What's in the Pack

• skill.md — Orchestrator skill that defines the 5-phase workflow (requirements → normalization → modeling → validation → implementation) and references all templates, references, scripts, validators, and examples
• templates/prisma-schema.prisma — Production-grade Prisma schema template with proper conventions: UUID v7 primary keys, soft deletes, audit timestamps, composite indexes, referential actions, and relation definitions
• templates/migration-template.sql — Production SQL migration template with proper transaction wrapping, index creation, constraint naming conventions, and rollback comments
• references/normalization-theory.md — Canonical knowledge on 1NF, 2NF, 3NF, BCNF — definitions, violation examples, insertion/update/deletion anomalies, and when to denormalize for query performance
• references/indexing-strategies.md — Canonical knowledge on indexing: B-tree vs GIN vs GiST, composite index ordering, covering indexes, partial indexes, when NOT to index, and Prisma @index/@unique conventions
• references/prisma-relational-modeling.md — Prisma-specific relational modeling patterns: relation definitions, referential actions (cascade/setNull/setDefault/noAction/restrict), composite keys, many-to-many, self-referencing, and @relation attribute usage
• references/postgresql-best-practices.md — PostgreSQL-specific schema best practices: UUID v7 vs v4 vs BIGINT identity, ENUM types, JSONB for semi-structured data, partial indexes, and schema organization
• scripts/validate-schema.sh — Executable script that validates a Prisma schema file for syntax errors, missing required fields, naming convention violations, and common anti-patterns; exits non-zero on failure
• scripts/scaffold-project.sh — Executable script that scaffolds a new database project with proper directory structure, initial Prisma schema, migration setup, and validation hooks
• validators/schema-lint.py — Python validator that parses Prisma schema AST and enforces rules: all models have @id, no string primary keys without @db.Uuid, relations are properly defined, naming conventions; exits 1 on violations
• validators/normalization-checker.py — Python validator that analyzes model relationships for normalization violations: detects transitive dependencies, repeating groups, and suggests 3NF corrections; exits 1 if violations found
• tests/schema-validation.test.sh — Test script that runs all validators against example schemas, checks exit codes, and validates that the schema-lint and normalization-checker correctly pass valid schemas and fail invalid ones
• examples/ecommerce-schema.prisma — Complete worked example: ecommerce schema with User, Product, Order, OrderItem, Category, Review models demonstrating 3NF, proper relations, indexes, and constraints
• examples/ecommerce-migration.sql — Corresponding SQL migration for the ecommerce example showing CREATE TABLE, ALTER TABLE, CREATE INDEX, and constraint definitions matching the Prisma schema
• examples/anti-patterns.md — Documented anti-patterns with before/after examples: string PKs without UUID, missing indexes on FKs, N+1 relation patterns, over-normalization, and missing soft-delete columns

Ship with Confidence

Stop shipping migrations that lock your production database. Start designing schemas that scale. Upgrade to Pro to install the Designing Database Schema skill and enforce a workflow that catches errors before they reach production.