Normalization vs Denormalization Decision Prompt

This prompt is for backend and data engineers who need a structured trade-off analysis between normalizing a data model for write integrity and denormalizing it for read performance. Use it when you have a specific access pattern, a proposed or existing schema, and you need a decision grounded in workload characteristics rather than generic rules. The prompt forces the model to reason about update anomalies, join costs, data duplication risks, and the operational burden of each approach. It is not a substitute for benchmarking, but it provides a rigorous starting point for an architecture decision record.

Do not use this prompt for trivial schema decisions where the trade-offs are obvious, or for systems where the access patterns are not yet known. The prompt requires concrete inputs: a description of the primary read and write patterns, the entities and relationships involved, and any non-functional constraints like latency budgets or consistency requirements. Without these, the model will produce a generic textbook answer that adds no value. The ideal user is someone who can describe their workload in terms of frequency, selectivity, and data freshness requirements, and who needs a structured analysis to document a decision.

Before running this prompt, gather the specific queries, mutations, and expected data volumes. The output should be treated as a decision-support artifact, not a final ruling. Always validate the model's reasoning against your own benchmarks and operational context. If the analysis recommends denormalization, pay special attention to the proposed synchronization strategy—this is where most production incidents originate.

This prompt is designed to be pasted directly into your AI harness. It forces the model to reason about a specific data model and access pattern rather than reciting textbook normalization rules. Replace every square-bracket placeholder with your concrete context before sending. The prompt expects a single-turn, structured JSON output that you can parse and feed into downstream decision records or review workflows.

textCopy
You are a senior database architect reviewing a data model decision. Your task is to produce a trade-off analysis comparing a normalized design against a denormalized design for the given access pattern. Do not apply generic rules. Reason from the specific workload characteristics provided.

## INPUT
- Entity and attribute description: [ENTITY_DESCRIPTION]
- Primary access pattern (reads): [READ_ACCESS_PATTERN]
- Write patterns and frequency: [WRITE_PATTERNS]
- Expected data volume and growth rate: [DATA_VOLUME]
- Consistency requirements (strict, eventual, read-your-writes): [CONSISTENCY_REQUIREMENTS]
- Query latency budget: [LATENCY_BUDGET]
- Existing schema constraints or dependencies: [EXISTING_CONSTRAINTS]

## CONSTRAINTS
- Consider at least three normalization forms: fully normalized (3NF/BCNF), partially denormalized, and fully denormalized.
- For each option, address write amplification, read complexity, storage cost, and schema evolvability.
- If the access pattern is read-heavy with few writes, explain why denormalization may be acceptable.
- If the access pattern is write-heavy or requires strict consistency, explain why normalization protects integrity.
- Flag any risk of update anomalies, phantom reads, or stale data.
- If the decision is close, recommend a hybrid approach and specify which fields to denormalize.

## OUTPUT_SCHEMA
Return a single JSON object with this exact structure:
{
  "recommendation": "normalized" | "denormalized" | "hybrid",
  "confidence": "high" | "medium" | "low",
  "options": [
    {
      "design": "fully_normalized" | "partially_denormalized" | "fully_denormalized",
      "write_impact": "string describing write amplification and anomaly risk",
      "read_impact": "string describing query complexity and join cost",
      "storage_impact": "string describing duplication and index overhead",
      "evolvability": "string describing schema change difficulty"
    }
  ],
  "trade_off_summary": "string summarizing the key tension and why the recommendation was chosen",
  "hybrid_specification": "string or null. If hybrid, specify which fields to denormalize and which to keep normalized",
  "risks": ["list of specific risks for the recommended design"],
  "migration_notes": "string describing what a migration from the current state to the recommended design would require"
}

## EXAMPLES
Example input: User profile with address, order history displayed on a dashboard. Reads are 1000:1 over writes. Latency budget 50ms.
Example output: Recommendation is partially_denormalized with address fields embedded in the user document but order history kept in a separate table with a foreign key.

Now analyze the provided input and return only the JSON object.

Adaptation notes: If your model does not reliably produce valid JSON, add a second turn or a post-processing step that validates the schema and retries with the error message. For high-stakes schema decisions, route the output to a human review queue before applying the recommendation. If you are using a model with a small context window, remove the examples block and rely on the schema description alone. The [EXISTING_CONSTRAINTS] placeholder should include any ORM limitations, framework conventions, or team skill constraints that could make a theoretically optimal design impractical to implement.

What to do next: After pasting this prompt with your context, validate the output JSON against the schema. If the confidence field is low, treat the analysis as a discussion starter rather than a decision. Pair this prompt with a peer review workflow where a second engineer reviews the trade-off summary and risks before any migration begins. For teams using Architecture Decision Records, the output fields map directly to ADR sections: Context, Options, Decision, and Consequences.

This prompt is not a one-shot answer generator; it is a structured reasoning tool that should be embedded in a design review pipeline. The harness must enforce that the model receives concrete access patterns, data volumes, and consistency requirements before producing a recommendation. Wire the prompt into your architecture review tooling by first collecting the required inputs—read/write ratios, query shapes, latency targets, and integrity constraints—from the proposing engineer. Feed these into the prompt template as structured context, not free-text prose, to prevent the model from defaulting to generic normalization rules. The output should be treated as a draft trade-off analysis that requires human review before any schema change is approved.

Validation and retry logic is critical because normalization decisions carry long-term performance and correctness consequences. Implement a post-generation validator that checks for: (1) presence of all required sections—access pattern summary, trade-off matrix, recommendation, and risks; (2) concrete numbers or ranges in the trade-off analysis rather than vague adjectives like 'fast' or 'slow'; (3) explicit acknowledgment of the provided access patterns rather than generic database theory; and (4) a clear statement of what the recommendation optimizes for. If validation fails, retry with a more constrained prompt that includes the specific validation errors. Set a maximum of two retries before escalating to a human reviewer. Log every generation attempt, validation result, and retry decision for auditability.

Model choice and tool integration matter here. Use a model with strong reasoning capabilities—GPT-4o, Claude 3.5 Sonnet, or equivalent—because the task requires multi-factor trade-off reasoning, not pattern matching. Do not use a small or quantized model for this workflow. If your organization maintains a data dictionary, schema registry, or query log analysis tool, wire those as retrieval sources before the prompt runs. The model should receive actual table sizes, index definitions, and query frequency data from your observability stack, not just the engineer's description. For high-risk systems (financial ledgers, healthcare records, multi-tenant data stores), add a mandatory human approval step after generation. The approval UI should display the model's recommendation alongside the raw inputs and validation results, enabling the reviewer to spot mismatches between the stated access patterns and the model's reasoning. Never auto-apply schema changes from this prompt's output.