---
name: database-schema-builder
description: "Converts fully specified tables, columns, and relationships into exact, dialect-correct SQL DDL, so a design agreed on paper becomes a script that runs first time. Use when tables, columns, keys, and relationships are already decided and the user wants exact CREATE TABLE and CREATE INDEX statements for one SQL dialect. Do not use for deciding what tables or columns a system needs, migrating an existing schema, or generating ORM model classes. Related topics: coding, database."
---

# Database Schema Builder

Converts fully specified tables, columns, and relationships into exact, dialect-correct SQL DDL, so a design agreed on paper becomes a script that runs first time.

## When to use

Use this skill when tables, columns, keys, and relationships are already decided and the user wants exact CREATE TABLE and CREATE INDEX statements for one SQL dialect. Do not use it for deciding what tables or columns a system needs, migrating an existing schema, or generating ORM model classes.

## 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. Map every specified column type to the dialect's native type and apply the naming convention to all identifiers.
3. Implement the primary key style with the dialect's mechanism and create a junction table for each many-to-many.
4. Flag any relationship, type, or key that cannot be built as written, instead of silently changing the specification.
5. Order the CREATE TABLE statements by dependency, then the CREATE INDEX statements, and confirm the script runs top to bottom.
6. Write the summary table (table, column count, primary key, foreign keys) and the list of flagged items.

## Inputs

- **Database system** (choose one: MySQL, PostgreSQL, SQLite, SQL Server, Oracle)
- **Entities (tables)** (longer free text)
- **Attributes per entity** (longer free text)
- **Primary key style** (choose one: Auto-increment, UUID, Composite, Natural)
- **Relationships** (longer free text)
- **Indexes** (longer free text)
- **Naming convention** (choose one: snake_case, camelCase, PascalCase, Keep as given)

## Output

A Markdown document with a summary table, then fenced sql blocks for CREATE TABLE statements in dependency order and CREATE INDEX statements, then a bullet list of flagged specification problems.

## Prompt template

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

### Database Schema Builder

Generate the SQL schema for these fully specified tables.

- Database system: {{Database system}}
- Entities (tables): {{Entities (tables)}}
- Attributes per entity: {{Attributes per entity}}
- Primary key style: {{Primary key style}}
- Relationships: {{Relationships}}
- Indexes: {{Indexes}}
- Naming convention: {{Naming convention}}

Translate the specification exactly: do not add or drop columns, but do flag anything that cannot work as written (a one-to-many with no foreign key column, a type the dialect lacks, a composite key on a table with no listed columns). Map every type to the dialect's native equivalent and spell out the primary key mechanism (SERIAL or IDENTITY or AUTOINCREMENT or a UUID default) for the chosen style. Create a junction table for each many-to-many using both table names. Apply the naming convention to every identifier. Order the CREATE TABLE statements so referenced tables come first, then the CREATE INDEX statements, so the script runs top to bottom without errors. Precede the SQL with a summary table of table name, column count, primary key, and foreign keys, and follow it with the list of anything you flagged.

## Pitfalls

- Adding helpful columns the specification did not list, which breaks the agreement the spec represents.
- Using a type that exists in one dialect but not the target, such as SERIAL on MySQL or DATETIME2 on PostgreSQL.
- Declaring foreign keys to tables created later in the script, so the first run fails.
