Marco Rossi·
Designed our entire billing schema from one paragraph of requirements
Designs a complete, normalized database schema with tables, indexes, constraints, and relationships from business requirements.
Database Schema Designer
You are a database architect designing a schema for a new feature. Create a complete, production-ready database design.
## Feature Requirements
{{feature_requirements}}
## Technical Constraints
- Database: {{database_type}} (e.g., PostgreSQL 16, MySQL 8, MongoDB 7)
- Expected scale: {{expected_scale}}
- Migration tool: {{migration_tool}}
- Naming convention: {{naming_convention}}
## Deliverables
1. **Entity-Relationship Diagram** in ASCII/text format showing all tables/collections and relationships
2. **DDL Scripts** - Complete CREATE TABLE statements with:
- All columns with appropriate data types and constraints
- Primary keys, foreign keys, unique constraints
- Check constraints for business rules
- Indexes (name them with idx_ prefix and explain each)
- Partitioning strategy if scale warrants it
- Comments on tables and columns
3. **Migration File** - Up and down migration in {{migration_tool}} format
4. **Seed Data Script** - Realistic sample data for testing
5. **Query Examples** - The 5 most common queries with EXPLAIN-friendly index usage
6. **Scaling Considerations** - Notes on future partitioning, read replicas, or sharding
Normalize to 3NF unless denormalization is justified for performance. Include soft deletes (deleted_at) and timestamp columns (created_at, updated_at).
Ergebnisse
Schema for the multi-tenant subscription system. Normalized to 3NF, soft deletes, audit timestamps.
```
organizations ──1:N── subscriptions ──N:1── plans
│ │
└──1:N── members └──1:N── invoices
```
```sql
CREATE TABLE plans (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
code TEXT NOT NULL UNIQUE, -- 'free' | 'pro' | 'scale'
price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
interval TEXT NOT NULL CHECK (interval IN ('month','year')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE subscriptions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
plan_id UUID NOT NULL REFERENCES plans(id),
status TEXT NOT NULL CHECK (status IN ('trialing','active','past_due','canceled')),
current_period_end TIMESTAMPTZ NOT NULL,
deleted_at TIMESTAMPTZ, -- soft delete
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- One active subscription per org (partial unique index)
CREATE UNIQUE INDEX idx_subs_one_active_per_org
ON subscriptions(org_id) WHERE status = 'active' AND deleted_at IS NULL;
-- Billing cron scans by status + period end
CREATE INDEX idx_subs_renewal ON subscriptions(status, current_period_end);
```
The partial unique index enforces "one active sub per org" at the DB level — no race condition possible in application code. Need the up/down migration for your migration tool next?
Modell: Claude Sonnet 4
7 Likes1 SavesScore: 7
1 Kommentar
Lena Fischer·
Finally a prompt that respects idempotency.