Database Schema Designer — Normalized & Production-Ready
Design normalized database schemas with proper indexes, constraints, and migration scripts for any application domain.
You are a database architect. Design a production-ready schema for: **Application:** [DESCRIBE YOUR APP] **Database:** [PostgreSQL / MySQL / MongoDB] **Scale:** [USERS: 1K / 100K / 1M+] **Core entities:** [LIST 4-6 main concepts] Deliver: ### 1. Entity-Relationship Diagram (text format) Show relationships: one-to-one, one-to-many, many-to-many ### 2. Table/Collection Definitions For each entity: ```sql CREATE TABLE [name] ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- columns with constraints created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); ``` ### 3. Indexes Strategy ```sql -- Explain the query pattern each index supports CREATE INDEX idx_[table]_[col] ON [table]([col]); ``` ### 4. Constraints & Data Integrity - Foreign keys with ON DELETE strategy - CHECK constraints - UNIQUE constraints ### 5. Soft Delete Pattern If applicable, explain approach ### 6. Migration File (Prisma or raw SQL) ### 7. Potential Scaling Issues - Tables likely to be large - Partitioning candidates - Archive strategy for old data
Result Text
## Schema — Multi-tenant SaaS (Project Management) ### ERD (simplified) ``` Organizations (1) ──< (N) Users Users (N) ──< (N) Projects [via project_members] Projects (1) ──< (N) Tasks Tasks (1) ──< (N) Comments Tasks (N) ──< (N) Tags [via task_tags] ``` ### Core Tables ```sql CREATE TABLE organizations ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), slug TEXT UNIQUE NOT NULL, name TEXT NOT NULL, plan TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free','pro','enterprise')), created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE TABLE tasks ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE, assignee_id UUID REFERENCES users(id) ON DELETE SET NULL, title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 500), status TEXT NOT NULL DEFAULT 'todo' CHECK (status IN ('todo','in_progress','done','cancelled')), priority SMALLINT DEFAULT 2 CHECK (priority BETWEEN 1 AND 5), due_date DATE, deleted_at TIMESTAMPTZ, -- soft delete created_at TIMESTAMPTZ DEFAULT NOW() ); -- Composite index for the most common query: project's open tasks by priority CREATE INDEX idx_tasks_project_status_priority ON tasks(project_id, status, priority DESC) WHERE deleted_at IS NULL; ```
评论
to leave a comment.
暂无评论，成为第一个评论的人！