database-schema-design

DESIGN normalized, scalable relational database schema before writing backend code. Enforce 3NF, UUIDv7 primary keys, foreign keys with cascade rules, and query indexes. Prevent denormalized data, missing constraints, and unindexed queries. Trigger: "design (the|a) database", "create (the|a) schema", "set up (Prisma|Drizzle)", "plan (the|the) tables".

Jump to install

Repository: ChloeVPin/candy-skills
Last source activity: June 29, 2026 at 23:04
Detected SKILL.md language: English
Stars: 0
Forks: 0

Install options

The review-first prompt is selected by default. You can switch to a direct command or download a local copy.

Review the source files

Read SKILL.md and any companion files shown by SkillsMP before deciding whether to install.

Back to search Back to installation

name	Database Schema Design
description	DESIGN normalized, scalable relational database schema before writing backend code. Enforce 3NF, UUIDv7 primary keys, foreign keys with cascade rules, and query indexes. Prevent denormalized data, missing constraints, and unindexed queries. Trigger: "design (the\|a) database", "create (the\|a) schema", "set up (Prisma\|Drizzle)", "plan (the\|the) tables".
category	backend
version	3.0.0
last_updated	2026-06-28T00:00:00.000Z
stacks	["PostgreSQL 17","Prisma 7","Drizzle ORM 0.45+","MySQL","SQLite"]
triggers	[{"pattern":"design (the\|a) (database\|schema\|tables)","action":"RUN schema design workflow"},{"pattern":"set up (Prisma\|Drizzle)","action":"RUN schema design workflow"},{"pattern":"plan (the\|the) tables","action":"RUN schema design workflow"},{"pattern":"project requires data persistence","action":"RUN schema design before any API route code"}]
related_skills	["api-route-structure","backend-validation-layers","saas-app-structure"]

Database Schema Design

IDENTIFY: When to Activate

Activate when:

Planning a new feature that persists complex relational data
User says "design the database", "create tables", "set up Prisma/Drizzle"
During architectural planning phase (after requirements discovery)

DECIDE: Schema Design Path

IF greenfield project →
  RUN Steps 1-7 in order

IF adding tables to existing schema →
  SCAN existing schema first (schema.prisma or drizzle schema)
  FOLLOW existing conventions (UUID vs auto-increment, naming style, timestamps)
  RUN Steps 1-7, but skip decisions already made by existing schema

IF NoSQL required (document-based, key-value) →
  STOP, this skill is for relational databases only
  Use JSONB with caution (see EXECUTE Step 8)

EXECUTE: Instructions

Step 1: Identify Entities

List every distinct "thing" the application tracks. Each becomes a table.

IDENTIFICATION RULES:
- Each entity is a noun: User, Order, Product, Comment, Tag
- Each entity gets ONE table
- If a noun cannot exist independently → it's an attribute, not an entity

OUTPUT FORMAT:
[Table]: [columns], [purpose]
Example:
users: id, email, name, created_at, updated_at, system users
posts: id, title, content, author_id, published_at, created_at, updated_at, blog posts

Step 2: Define Relationships

Map connections between entities:

RELATIONSHIP TYPES:
1:1  → Foreign key on either side + UNIQUE constraint
1:N  → Foreign key on the "many" side (post.comment_id → comment.id)
N:M  → Bridge table (post_tags: post_id + tag_id composite PK)

DIAGRAM FORMAT:
[TableA] [N]───[N] [TableB]    , many-to-many (N:M)
[TableA] 1───N [TableB]        , one-to-many (1:N)
[TableA] 1───1 [TableB]        , one-to-one (1:1)

Step 3: Choose Primary Keys

DEFAULT: UUIDv7 (time-ordered, B-tree friendly, distributed-safe)

UUIDv7 advantages:
- Millisecond-precision timestamp prefix → sequential inserts, no B-tree splits
- Random suffix → collision-free across shards/regions
- No coordination between servers needed

ALTERNATIVES (use only when condition is met):
- Auto-increment integers: ONLY for small single-server apps needing short, human-readable IDs
- UUIDv4: ONLY for opaque tokens (API keys, session IDs) where time-ordering is undesirable
- CUID2: Acceptable alternative to UUIDv7 for Prisma users (Prisma 7 defaults to cuid())
- NanoID: Acceptable for short, URL-safe identifiers where collisions are tolerable

Prisma 7 (pure TypeScript):

model User {
  id        String   @id @default(cuid())  // CUID2, time-ordered
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

import { pgTable, uuid, varchar, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

1NF, No repeating groups:
  ❌ post_tags: "javascript,react,typescript" (CSV in one column)
  ✅ Separate post_tags table, one row per tag

2NF, Non-key columns depend on whole PK (composite keys only):
  ❌ order_items: order_id, product_id, order_date (order_date depends on order_id, not (order_id, product_id))
  ✅ order_items: order_id, product_id. order_date in orders table.

3NF, No transitive dependencies:
  ❌ orders: user_id, user_email, user_name (user_email depends on user_id, not order_id)
  ✅ orders: user_id only. User details in users table.

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  -- CASCADE: child deleted when parent deleted
  -- SET NULL: child preserved, relationship severed
  -- RESTRICT: prevent deletion if children exist
);

IF child cannot exist without parent (post comments when post deleted)  → ON DELETE CASCADE
IF child can exist without parent but loses relationship               → ON DELETE SET NULL
IF deletion must be blocked when children exist (org with members)      → ON DELETE RESTRICT

INDEX RULES:
- Index foreign keys (used in JOINs)
- Index filtered columns (WHERE clauses)
- Index sort columns (ORDER BY)
- Use composite indexes for common query patterns
- DO NOT over-index: each index slows writes

EXAMPLES:
CREATE INDEX idx_posts_author_id ON posts(author_id);                    -- FK index
CREATE INDEX idx_posts_published_at ON posts(published_at);              -- filter/sort
CREATE INDEX idx_posts_author_published ON posts(author_id, published_at DESC);  -- composite

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-update trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

USE JSONB for:
  ✅ User preferences / settings (varying schema per user)
  ✅ Raw webhook payloads (external schema, not controlled)
  ✅ Audit logs (capture full state at point in time)

NEVER USE JSONB for:
  ❌ Relational data: posts, comments, orders → use separate tables
  ❌ Data queried with WHERE clauses → index performance degrades
  ❌ Data with stable, known structure → use typed columns

Data Type	Use	Never Use
Emails	`VARCHAR(255)`	`TEXT` (no length limit)
Names	`VARCHAR(100)`	Larger than needed
Booleans	`BOOLEAN`	Integer 0/1
Money	`DECIMAL(10,2)` or `BIGINT` (cents)	`FLOAT` (rounding errors)
Dates/times	`TIMESTAMPTZ` (PostgreSQL)	`TIMESTAMP` (no timezone)
Long text	`TEXT`	`VARCHAR(10000)`
JSON	`JSONB` (PostgreSQL 17 with JSON_TABLE support)	`JSON` (no indexing)

USE soft deletes ONLY when:
- Audit/compliance requires recoverable deletion
- "Trash" feature lets users recover items
- Analytics on deleted data is required

DO NOT use soft deletes as default, every query needs WHERE is_deleted = false
Prefer hard deletes with ON DELETE CASCADE for most applications

interface SchemaDesign {
  entities: Array<{
    name: string;
    columns: Array<{ name: string; type: string; constraints: string[] }>;
    purpose: string;
  }>;
  relationships: Array<{
    from: string;
    to: string;
    type: '1:1' | '1:N' | 'N:M';
    cascade: 'CASCADE' | 'SET NULL' | 'RESTRICT';
  }>;
  indexes: Array<{
    table: string;
    columns: string[];
    type: 'unique' | 'composite' | 'standard';
  }>;
}

Anti-Pattern	Detection Signal	Correction
CSV in column (`"tag1,tag2"`)	Column stores comma-separated values	Create bridge table for M:N
Integer IDs with no FK	Column named `user_id` but no REFERENCES	Always declare `REFERENCES`
No indexes on large tables	Table expected >10K rows, no CREATE INDEX	Index WHERE/JOIN/ORDER BY columns
`TEXT` for everything	All columns are TEXT with no length limits	Use VARCHAR(N) with appropriate bounds
`is_deleted` on every table	Every query has WHERE is_deleted = false	Only when business requires