---
name: data-model-review
description: "Checks a database design for the entities, relationships, constraints and indexes it needs, judged against the queries the application actually runs. Use when a database schema or document model needs checking for relationships, constraints and indexes against the queries the app really runs. Do not use for writing the SQL queries themselves or choosing a database product. Related topics: data, coding."
---

# Data Model Review

Checks a database design for the entities, relationships, constraints and indexes it needs, judged against the queries the application actually runs.

## When to use

Use this skill when a database schema or document model needs checking for relationships, constraints and indexes against the queries the app really runs. Do not use it for writing the SQL queries themselves or choosing a database product.

## 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 every access pattern with the fields it filters, sorts and joins on, plus its rough volume.
3. Check entities and relationships so each fact lives in one place and each record has a clear owner.
4. Review constraints, delete behaviour, types and naming, and flag traps such as money in floats or free-text states.
5. Propose an index for each access pattern with its column order and name any redundant existing indexes.
6. Rank the changes and give a migration order that avoids downtime, with the risk of each.

## Inputs

- **Schema or model** (longer free text)
- **Database** (choose one: PostgreSQL, MySQL, Firestore, MongoDB, SQLite, Other)
- **Main access patterns** (longer free text)
- **Expected size** (free text, e.g. "Rows or documents in a year")
- **Rules the data must obey** (longer free text)
- **Known pain** (longer free text)

## Output

An access pattern table, findings grouped by relationships, constraints and types, an index plan, and a ranked change list with a migration order.

## Prompt template

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

### Data Model Review

Review this data model against how it will be used.

- Schema or model: {{Schema or model}}
- Database: {{Database}}
- Main access patterns: {{Main access patterns}}
- Expected size: {{Expected size}}
- Rules the data must obey: {{Rules the data must obey}}
- Known pain: {{Known pain}}

Start from the access patterns, not the schema: list each query or write and the fields it filters, sorts and joins on. Then check the entities and relationships: one fact stored in one place, many-to-many relations through a link table or array, and clear ownership of each record. Check constraints: primary keys, foreign keys, unique and not-null rules, check constraints for allowed states, and what happens on delete. Review types and naming for traps such as money in floats, time zones, free-text status fields, and nullable columns that carry meaning. Propose indexes for each access pattern, show the composite order and say which existing indexes are unused or redundant. For document databases, judge embedding against referencing by read and update frequency. Finish with a prioritised list of changes, a migration order that avoids downtime, and the risks of each.

## Pitfalls

- Adding an index for every column, which slows writes and still misses the composite order queries need.
- Judging the schema without access patterns, so normalisation is optimised for neatness instead of use.
- Changing a column type in place on a large table, which locks it and causes the downtime the plan promised to avoid.