---
name: sql-schema-designer
description: "Designs a relational schema from an app description and returns the reasoning plus ready-to-run CREATE TABLE statements, with keys, constraints and optional sample data. Use when a developer is starting an app and needs tables, keys and constraints worked out from a plain description of its entities. Do not use for optimizing queries on an existing schema or choosing a join. Related topics: sql, database, architecture."
---

# SQL Schema Designer

Designs a relational schema from an app description and returns the reasoning plus ready-to-run CREATE TABLE statements, with keys, constraints and optional sample data.

## When to use

Use this skill when a developer is starting an app and needs tables, keys and constraints worked out from a plain description of its entities. Do not use it for optimizing queries on an existing schema or choosing a join.

## 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. List entities and relationships from the description and mark which need join tables.
3. Normalize the design and note any deliberate denormalization with the reason.
4. Write CREATE TABLE statements in dependency order with native types, constraints and ON DELETE rules.
5. Add the requested indexes, timestamps and sample INSERTs, then confirm the script would run top to bottom.

## Inputs

- **App and main entities** (longer free text)
- **Database system** (choose one: PostgreSQL, MySQL, SQLite, SQL Server)
- **Use UUIDs for primary keys** (choose one: Yes, No)
- **Elements to include** (choose any: Foreign keys, Indexes, Timestamps, Constraints)
- **Include sample INSERT statements** (choose one: Yes, No)

## Output

A brief design explanation followed by the complete SQL for the chosen database in dependency order, with optional sample INSERT statements.

## Prompt template

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

### Relational Schema Designer

Design the database schema for the application below.

- App and main entities: {{App and main entities}}
- Database system: {{Database system}}
- Use UUIDs for primary keys: {{Use UUIDs for primary keys}}
- Elements to include: {{Elements to include}}
- Include sample INSERT statements: {{Include sample INSERT statements}}

Start by listing the entities and the relationships between them (one to many, many to many) and say where a join table is needed. Normalize to third normal form unless a denormalized column has a clear read benefit, and say when you make that call. Choose column types native to the chosen database. Add NOT NULL, UNIQUE and CHECK constraints where the domain implies them, foreign keys with explicit ON DELETE behaviour, and indexes on foreign keys and commonly filtered columns.

Return a short explanation of the design, then the full SQL in dependency order so it runs top to bottom. If sample data was requested, add INSERT statements that respect the foreign keys.

## Pitfalls

- Creating tables before the ones they reference, which makes the script fail.
- Omitting ON DELETE rules so deletes either fail or orphan child rows.
- Using types from another database, such as UUID on SQLite or SERIAL on MySQL.
