Schema Design — Animate | AI Wisdom

← Back to topic
🎞 ANIMATE · Mode 3 of 5 · +100 XP— Database Schema Design
📋PK Design
⚙️Constraints
🔄FK
✅Indexes
🎯 Next: Test →
Step 1 / 4
Speed
PK & Timestamps
UUID PK, soft delete with deleted_at, and created_at/updated_at for auditing💡 Analogy
Like a document lifecycle — creation date, last modified date, and archive date (not physical deletion).
UUID PKs avoid sequential ID guessing. Soft delete (deleted_at) preserves history. Audit timestamps track when rows were created/updated.
Console Output
> id: UUID (random, globally unique)
> deleted_at: NULL = active, timestamp = deleted
> updated_at: trigger-updated on each change
●schema_design.sql
PK & TimestampsL3–11
1-- E-commerce schema with proper constraints and indexes
2 
3-- Users: immutable UUID PK, soft delete, audit timestamps
4CREATE TABLE users (
5    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
6    email      VARCHAR(255) UNIQUE NOT NULL,
7    name       VARCHAR(255)        NOT NULL,
8    deleted_at TIMESTAMPTZ,           -- soft delete
9    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
10    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
11);
12 
13-- Products: enum with CHECK, generated column
14CREATE TYPE product_status AS ENUM ('draft', 'active', 'archived');
15 
16CREATE TABLE products (
17    id         SERIAL PRIMARY KEY,
18    sku        VARCHAR(50) UNIQUE NOT NULL,
19    name       VARCHAR(255)       NOT NULL,
20    price_cents INT NOT NULL CHECK (price_cents >= 0),
21    status     product_status NOT NULL DEFAULT 'draft',
22    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
23);
24 
25-- Orders: FK with cascade, index for user queries
26CREATE TABLE orders (
27    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
28    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
29    status      VARCHAR(50) NOT NULL DEFAULT 'pending',
30    total_cents INT NOT NULL DEFAULT 0,
31    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
32);
33 
34-- Indexes: covering index for common query pattern
35CREATE INDEX idx_orders_user_created
36    ON orders (user_id, created_at DESC)
37    INCLUDE (status, total_cents);     -- covering index
38 
39-- Partial index: only index active products
40CREATE INDEX idx_products_active_price
41    ON products (price_cents)
42    WHERE status = 'active';
42 lines
Timeline — 4 Steps
PK & Timestamps
UUID PK, soft delete with deleted_at, and created_at/updated_at for auditing💡 Analogy
Like a document lifecycle — creation date, last modified date, and archive date (not physical deletion).
UUID PKs avoid sequential ID guessing. Soft delete (deleted_at) preserves history. Audit timestamps track when rows were created/updated.
Console Output
> id: UUID (random, globally unique)
> deleted_at: NULL = active, timestamp = deleted
> updated_at: trigger-updated on each change

Was this page helpful?

Discussion

UUID PK, soft delete with deleted_at, and created_at/updated_at for auditing

💡 Analogy

Like a document lifecycle — creation date, last modified date, and archive date (not physical deletion).

UUID PKs avoid sequential ID guessing. Soft delete (deleted_at) preserves history. Audit timestamps track when rows were created/updated.

Console Output

> id: UUID (random, globally unique)
> deleted_at: NULL = active, timestamp = deleted
> updated_at: trigger-updated on each change

UUID PK, soft delete with deleted_at, and created_at/updated_at for auditing

💡 Analogy

Like a document lifecycle — creation date, last modified date, and archive date (not physical deletion).

UUID PKs avoid sequential ID guessing. Soft delete (deleted_at) preserves history. Audit timestamps track when rows were created/updated.

Console Output

> id: UUID (random, globally unique)
> deleted_at: NULL = active, timestamp = deleted
> updated_at: trigger-updated on each change