What this prompt does
This prompt turns the model into a database architect that audits your schema and advises on where to normalize and where to strategically denormalize. You provide your [current_schema] plus application context — [app_type], [access_patterns], [scale_info], and [consistency_needs] — and it evaluates the design against normal forms, flags anomalies, and recommends targeted denormalizations with migration paths.
The structure keeps it honest. It checks 1NF through BCNF and, for each violation, names the specific insert, update, or delete anomaly it can cause. Then it evaluates denormalization patterns (computed columns, redundant columns, summary tables, JSON columns, materialized views) in a trade-off table, gated by a decision tree that weighs your read/write ratio and [consistency_needs]. Because you supply [access_patterns] and [scale_info], it can tell you when a JOIN is worth removing and when consistency should win. The [additional_pattern] slot lets you pose a specific question, like whether polymorphic relationships belong in shared or per-type tables.
When to use it
- When a data model has grown messy and you need to decide where denormalization actually pays off.
- To audit a schema against normal forms and find anomalies before they corrupt data.
- When JOINs are causing measurable latency and you're weighing a redundant column or summary table.
- Before scaling a read-heavy product where pre-aggregation or materialized views might help.
- To get concrete, reversible migration steps instead of a risky one-shot schema change.
Example output
You get a normalization audit (1NF/2NF/3NF/BCNF) with each violation, the anomaly it causes, and the ALTER/CREATE statements to fix it. Then a denormalization table with columns for the change, why, the trade-off, and when-to-use vs. when-not-to. It includes a decision framework, a five-step backward-compatible migration path with actual SQL, a before/after performance comparison on the top changes, and monitoring queries to detect staleness and consistency drift in denormalized data.
Pro tips
- Paste the full
[current_schema]including constraints and keys so the BCNF check can spot non-trivial functional dependencies. - Be specific in
[consistency_needs]: mark which tables must be strongly consistent (payments, orders) and which can tolerate staleness, so it never denormalizes the wrong data. - Describe
[access_patterns]by read vs. write intensity per area — the decision tree leans on the 10x-read heuristic to recommend denormalization. - Measure JOIN latency yourself before acting. I make the model insist on benchmarks because denormalization is a bet, not a default.
- Use
[additional_pattern]to ask about a real modeling dilemma you're stuck on, like polymorphic comments or tags. - Run the generated migration in stages (add, backfill, switch, verify, drop) and keep the validation period before removing old structures.
