---
name: database-schema-architect
description: "Takes a list of business entities and how they relate and returns a normalised relational design: an ER description, a table summary, and runnable CREATE TABLE statements with keys and indexes for your database. Use when a new relational schema must be designed from a list of business entities and relationships, with an ER description and runnable DDL. Do not use for writing DDL for tables whose columns are already fully specified, refactoring a live schema, or documenting an existing database. Related topics: coding, database."
---

# Database Schema Architect

Takes a list of business entities and how they relate and returns a normalised relational design: an ER description, a table summary, and runnable CREATE TABLE statements with keys and indexes for your database.

## When to use

Use this skill when a new relational schema must be designed from a list of business entities and relationships, with an ER description and runnable DDL. Do not use it for writing DDL for tables whose columns are already fully specified, refactoring a live schema, or documenting an existing database.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Derive columns and native types for each entity from its stated facts, deciding nullability per column, so each table reaches third normal form.
3. Resolve relationships: a junction table per many-to-many, the foreign key on the many side of each one-to-many, and the chosen primary key style applied throughout.
4. Add indexes for foreign keys and implied lookups within the chosen strategy, dropping any the stated scale does not justify.
5. Write the ER description and the table summary with purpose and row estimate per table.
6. Emit CREATE TABLE statements in dependency order with constraints, indexes, and audit columns, and list every decision made on the user's behalf.

## Inputs

- **Database platform** (choose one: MySQL, PostgreSQL, SQL Server, Oracle, SQLite)
- **Core entities** (longer free text)
- **Relationships** (longer free text)
- **Primary key style** (choose one: Auto-increment, UUID, Composite, Natural)
- **Index strategy** (choose any: Single-column, Composite, Full-text, Spatial, Minimal)
- **Include audit columns (creation and update timestamps plus creator)** (choose one: Yes, No)
- **Expected scale** (free text, e.g. "10k customers and 1M orders per year")

## Output

A Markdown document with a textual ER description, a table summary list, and one fenced sql block of CREATE TABLE statements in dependency order with keys, constraints, and indexes, followed by a list of design decisions.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Database Schema Architect

Design a relational schema from these entities and relationships and give me the DDL.

- Database platform: {{Database platform}}
- Core entities: {{Core entities}}
- Relationships: {{Relationships}}
- Primary key style: {{Primary key style}}
- Index strategy: {{Index strategy}}
- Include audit columns (creation and update timestamps plus creator): {{Include audit columns (creation and update timestamps plus creator)}}
- Expected scale: {{Expected scale}}

Turn each entity into a table in third normal form: derive the columns from the facts listed, pick types native to the platform, and make nullability a decision rather than a default. Resolve every many-to-many through a junction table named after both sides, and put the foreign key on the many side of each one-to-many. Apply the primary key style consistently and note where a natural key would be fragile. Add indexes for every foreign key and for the lookups the relationships imply, keeping to the chosen strategy, and skip indexes that the scale does not justify. Describe the ER model in text first (one line per table with its relationships and cardinalities), then a table summary with purpose and row estimate, then a single fenced sql block with CREATE TABLE statements in dependency order so the script runs top to bottom, including constraints, indexes, and audit columns when requested. State any design decision you made on the user's behalf.

## Pitfalls

- Emitting tables in alphabetical order so a foreign key references a table that does not exist yet and the script fails halfway.
- Modelling a many-to-many with an array or comma-separated column instead of a junction table, which cannot be joined or indexed.
- Indexing every column on a small table, which slows writes for no read benefit.
