Design database schemas — tables, relationships, indexes, constraints, and ORM setup. Covers relational design, normalization, and common patterns.
Design a database schema from requirements.
From the requirements, extract the core entities (nouns):
| Relationship | Implementation |
|---|---|
| One-to-one | Foreign key with unique constraint, or embed in same table |
| One-to-many | Foreign key on the "many" side |
| Many-to-many | Junction/join table |
| Self-referential | Foreign key pointing to same table (e.g. parent_id) |
For each table:
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
avatar_url TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Primary keys:
UUID for distributed systems or public-facing IDsSERIAL/BIGSERIAL for internal-only IDs (faster joins)Timestamps:
created_at and updated_atTIMESTAMPTZ (with timezone), never TIMESTAMPNaming:
users, project_members)user_id, created_at)idx_<table>_<columns> (idx_users_email)Constraints:
NOT NULL on everything unless it's genuinely optionalUNIQUE on natural keys (email, slug, external IDs)REFERENCES with ON DELETE behavior (CASCADE, SET NULL, RESTRICT)CHECK constraints for enums or value ranges-- For columns you filter/sort by frequently
CREATE INDEX idx_projects_owner_id ON projects(owner_id);
-- For unique lookups
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Composite for common query patterns
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);
When to index:
When NOT to index:
Prisma:
model User {
id String @id @default(uuid())
email String @unique
name String
projects Project[]
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
@@map("users")
}
Drizzle:
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull().unique(),
name: text('name').notNull(),
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});
Soft deletes: Add deleted_at TIMESTAMPTZ instead of actually deleting rows
Audit log: Separate audit_events table with entity_type, entity_id, action, actor_id, payload
Tags/labels: Junction table (task_tags) with task_id + tag_id
Tree/hierarchy: parent_id self-reference, or materialized path (/1/4/7/)
Polymorphic associations: Use entity_type + entity_id columns (avoid if possible, prefer separate FKs)