Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
[{"requirements":"Business requirements and entities to model"},{"existing_schema":"Current schema (if modifying existing database)"},{"orm":"Prisma | Drizzle | TypeORM | Sequelize | raw SQL (optional)"},{"database":"PostgreSQL | MySQL | SQLite | MongoDB (optional)"}]
outputs
[{"schema":"Database schema definition (SQL or ORM format)"},{"er_diagram":"Mermaid ER diagram of the schema"},{"normalization_analysis":"Normal form assessment with justification"},{"indexes":"Recommended indexes with query justification"},{"migration_plan":"Steps to migrate from current to target schema"}]
linksTo
["api-designer","mermaid","error-handling"]
linkedFrom
["orchestrator","planner","code-architect"]
preferredNextSkills
["api-designer","mermaid"]
fallbackSkills
["sequential-thinking"]
riskLevel
medium
memoryReadPolicy
selective
memoryWritePolicy
selective
sideEffects
[]
Data Modeling
Purpose
This skill designs database schemas that are normalized, performant, and maintainable. It covers entity-relationship modeling, normalization analysis (targeting BCNF as the standard), indexing strategies, and migration planning. Schemas are produced in both SQL and ORM-specific formats.
Key Concepts
Normalization Levels
1NF — First Normal Form:
✓ All columns contain atomic (indivisible) values
✓ Each row is unique (has a primary key)
✗ No repeating groups or arrays in columns
2NF — Second Normal Form:
✓ Meets 1NF
✓ No partial dependencies (all non-key columns depend on the ENTIRE primary key)
✗ Only relevant for composite primary keys
3NF — Third Normal Form:
✓ Meets 2NF
✓ No transitive dependencies (non-key columns don't depend on other non-key columns)
Example violation: orders table with customer_name derived from customer_id
BCNF — Boyce-Codd Normal Form (TARGET STANDARD):
✓ Meets 3NF
✓ Every determinant is a candidate key
✓ No anomalies possible from functional dependencies
When to Denormalize
Denormalization is acceptable ONLY when:
1. READ PERFORMANCE: A join-heavy query is a proven bottleneck (measured, not assumed)
2. MATERIALIZED DATA: Computed values that are expensive to derive (store alongside source)
3. EVENT SOURCING: Immutable event records that capture state at a point in time
4. CACHING LAYER: The denormalized data is a cache, not the source of truth
RULE: Start normalized. Denormalize only when you have benchmarks proving the need.
DOCUMENT: Every denormalization must have a comment explaining WHY.
Data Type Selection
IDENTIFIERS:
Primary keys → UUID (uuid/gen_random_uuid()) — no info leakage, merge-friendly
Alternative → ULID/CUID2 — sortable, no coordination needed
Avoid → Auto-increment integers for public-facing IDs
TEXT:
Short fixed → VARCHAR(N) with explicit limit (name VARCHAR(100))
Variable → TEXT (no practical limit, same performance as VARCHAR in PostgreSQL)
Enum-like → Create an ENUM type or use a lookup table
NUMBERS:
Money → INTEGER (store as cents/smallest unit) or DECIMAL(19,4)
Counters → INTEGER or BIGINT
Percentages → DECIMAL(5,2) for precision, or INTEGER (store as basis points)
Float math → NUMERIC/DECIMAL (never use FLOAT for financial data)
DATES:
Timestamps → TIMESTAMPTZ (always with timezone in PostgreSQL)
Dates only → DATE
Durations → INTERVAL or INTEGER (store as seconds)
BOOLEAN:
Binary state → BOOLEAN
Multi-state → ENUM or lookup table (future-proofs against "maybe" states)
JSON:
Semi-struct → JSONB (PostgreSQL) — when schema varies per row
Metadata → JSONB — for extensible key-value data
Avoid → Don't store relational data as JSON
Design Workflow
Phase 1: Entity Discovery
STEP 1: Identify nouns in the requirements
"Users can create organizations, invite members, and manage projects with tasks"
ENTITIES: User, Organization, Member(ship), Project, Task
STEP 2: Identify attributes for each entity
User: id, email, name, password_hash, created_at
Organization: id, name, slug, created_at
Project: id, name, description, status, org_id
Task: id, title, description, status, priority, project_id, assignee_id
STEP 3: Identify relationships
User ←→ Organization: many-to-many (through Membership)
Organization → Project: one-to-many
Project → Task: one-to-many
User → Task: one-to-many (assignee)
Phase 2: Relationship Modeling
ONE-TO-MANY:
Parent table (one) has no FK
Child table (many) has FK referencing parent
Example: project_id in tasks table → projects.id
MANY-TO-MANY:
Join table with FKs to both sides
May include relationship attributes (role, joined_at)
Example: memberships(user_id, org_id, role, joined_at)
ONE-TO-ONE:
FK on either side (put it on the dependent entity)
Or merge into one table if always loaded together
Example: user_profiles(user_id FK, bio, avatar_url)
SELF-REFERENTIAL:
FK on the same table
Example: comments(id, parent_comment_id FK, content)
POLYMORPHIC (AVOID IF POSSIBLE):
Instead of: commentable_type + commentable_id
Prefer: separate FKs (post_id, product_id) with CHECK constraint
Or: separate join tables (post_comments, product_comments)
Phase 3: Schema Definition
PostgreSQL Example
-- Enable UUID generationCREATE EXTENSION IF NOTEXISTS "pgcrypto";
-- Users tableCREATE TABLE users (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
CONSTRAINT users_email_unique UNIQUE (email),
CONSTRAINT users_email_format CHECK (email ~*'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$')
);
-- Organizations tableCREATE TABLE organizations (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
slug VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
CONSTRAINT organizations_slug_unique UNIQUE (slug),
CONSTRAINT organizations_slug_format CHECK (slug ~*'^[a-z0-9-]+$')
);
-- Memberships (join table with attributes)CREATE TYPE membership_role AS ENUM ('owner', 'admin', 'member', 'viewer');
CREATE TABLE memberships (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
user_id UUID NOT NULLREFERENCES users(id) ONDELETE CASCADE,
org_id UUID NOT NULLREFERENCES organizations(id) ONDELETE CASCADE,
role membership_role NOT NULLDEFAULT'member',
joined_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
CONSTRAINT memberships_user_org_unique UNIQUE (user_id, org_id)
);
-- ProjectsCREATE TYPE project_status AS ENUM ('active', 'archived', 'deleted');
CREATE TABLE projects (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
org_id UUID NOT NULLREFERENCES organizations(id) ONDELETE CASCADE,
name VARCHAR(200) NOT NULL,
description TEXT,
status project_status NOT NULLDEFAULT'active',
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
-- TasksCREATE TYPE task_status AS ENUM ('todo', 'in_progress', 'in_review', 'done');
CREATE TYPE task_priority AS ENUM ('low', 'medium', 'high', 'critical');
CREATE TABLE tasks (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
project_id UUID NOT NULLREFERENCES projects(id) ONDELETE CASCADE,
assignee_id UUID REFERENCES users(id) ONDELETESETNULL,
title VARCHAR(500) NOT NULL,
description TEXT,
status task_status NOT NULLDEFAULT'todo',
priority task_priority NOT NULLDEFAULT'medium',
due_date DATE,
position INTEGERNOT NULLDEFAULT0,
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
Prisma Example
model User {
id String @id @default(uuid()) @db.Uuid
email String @unique @db.VarChar(255)
name String @db.VarChar(100)
passwordHash String @map("password_hash") @db.VarChar(255)
createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz
updatedAt DateTime @updatedAt @map("updated_at") @db.Timestamptz
memberships Membership[]
assignedTasks Task[] @relation("TaskAssignee")
@@map("users")
}
model Organization {
id String @id @default(uuid()) @db.Uuid
name String @db.VarChar(100)
slug String @unique @db.VarChar(100)
createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz
updatedAt DateTime @updatedAt @map("updated_at") @db.Timestamptz
memberships Membership[]
projects Project[]
@@map("organizations")
}
model Membership {
id String @id @default(uuid()) @db.Uuid
userId String @map("user_id") @db.Uuid
orgId String @map("org_id") @db.Uuid
role MembershipRole @default(member)
joinedAt DateTime @default(now()) @map("joined_at") @db.Timestamptz
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
organization Organization @relation(fields: [orgId], references: [id], onDelete: Cascade)
@@unique([userId, orgId])
@@map("memberships")
}
enum MembershipRole {
owner
admin
member
viewer
}
Phase 4: Index Design
PRINCIPLE: Every index costs write performance. Add only for proven query patterns.
STEP 1: Identify query patterns
Q1: Get user by email (login) → users.email (already unique)
Q2: List org members → memberships.org_id
Q3: List project tasks by status → tasks(project_id, status)
Q4: List user's tasks across projects → tasks.assignee_id
Q5: Sort tasks by position within project → tasks(project_id, position)
STEP 2: Create indexes
-- Already covered by unique constraints:
-- users.email, organizations.slug, memberships(user_id, org_id)
-- Additional indexes for query patterns:
CREATE INDEX idx_memberships_org ON memberships(org_id);
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
CREATE INDEX idx_tasks_project_position ON tasks(project_id, position);
STEP 3: Consider partial indexes
-- Only index active projects (archived/deleted rarely queried)
CREATE INDEX idx_projects_org_active
ON projects(org_id)
WHERE status = 'active';
STEP 4: Consider covering indexes (for read-heavy queries)
-- Include frequently selected columns to avoid table lookup
CREATE INDEX idx_tasks_list
ON tasks(project_id, status)
INCLUDE (title, priority, assignee_id, position);
Migration Planning
Safe Migration Checklist
BEFORE MIGRATION:
[ ] Backup the database
[ ] Test migration on staging with production-sized data
[ ] Estimate migration duration
[ ] Plan rollback procedure
[ ] Identify if downtime is required
SAFE OPERATIONS (no downtime):
✓ Add a new table
✓ Add a new nullable column
✓ Add a new index (CONCURRENTLY in PostgreSQL)
✓ Add a new enum value
✓ Rename a table (if using views for backward compatibility)
UNSAFE OPERATIONS (require planning):
✗ Drop a column → First deploy code that doesn't read it, then drop
✗ Rename a column → Add new column, migrate data, update code, drop old
✗ Change column type → Add new column, migrate, swap
✗ Add NOT NULL to existing column → Add default first, backfill NULLs, then add constraint
✗ Add UNIQUE constraint → First verify no duplicates exist
Multi-Step Migration Pattern
For renaming column 'name' to 'full_name' on users table:
MIGRATION 1 (deploy):
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);
UPDATE users SET full_name = name;
-- Application reads from both, writes to both
MIGRATION 2 (deploy):
-- Application reads from full_name, writes to both
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
MIGRATION 3 (deploy):
-- Application only uses full_name
ALTER TABLE users DROP COLUMN name;
Normalization Analysis Template
TABLE: orders
COLUMNS: id, user_id, user_email, product_id, product_name, product_price, quantity, total
ANALYSIS:
1NF: ✓ All values are atomic
2NF: ✗ VIOLATION — user_email depends on user_id (partial dependency if composite key)
3NF: ✗ VIOLATION — user_email transitively depends on id through user_id
✗ VIOLATION — product_name, product_price depend on product_id
BCNF: ✗ (fails 3NF, so fails BCNF)
RESOLUTION:
Remove user_email → look up via users table join
Remove product_name, product_price → look up via products table join
OR: Keep product_price as "price_at_purchase" (intentional denormalization for historical accuracy)
NORMALIZED:
orders: id, user_id, quantity, total, created_at
order_items: id, order_id, product_id, quantity, unit_price_cents (snapshot at purchase time)
users: id, email, name
products: id, name, price_cents
Anti-Patterns
Entity-Attribute-Value (EAV): Using a generic key-value table instead of proper columns. Destroys type safety, query performance, and referential integrity. Use JSONB for truly dynamic data.
Polymorphic associations: commentable_type + commentable_id cannot have foreign key constraints. Use separate tables or separate FK columns.
Storing money as FLOAT: Floating-point arithmetic causes rounding errors. Use INTEGER (cents) or DECIMAL.
Missing timestamps: Tables without created_at and updated_at make debugging and auditing impossible.
No soft delete strategy: Hard deleting records breaks referential integrity and audit trails. Use deleted_at timestamp or a status enum.
Over-indexing: Indexes on every column slow down writes and waste storage. Index only for proven query patterns.
Timestamps without timezone: Using TIMESTAMP instead of TIMESTAMPTZ leads to timezone ambiguity bugs.
Integration Notes
Generate ER diagrams via mermaid for visual documentation of the schema.
Align schema design with api-designer to ensure API resources map cleanly to database entities.
Use error-handling to implement proper database error handling (unique violations, FK violations, etc.).
Validate normalization decisions with sequential-thinking for complex schemas.
