Tree-of-Thought Data Model Design Prompt

This prompt is designed for data architects and backend engineers facing high-stakes schema design decisions where a single recommendation would be dangerously shallow. The core job-to-be-done is generating a structured, branching comparison of multiple valid schema strategies—varying normalization levels, access patterns, and consistency models—and forcing an explicit trade-off analysis at each leaf node. Use it when the team is deadlocked on the right approach, when you need a rigorous artifact to support an Architecture Decision Record (ADR), or when the domain model involves more than five core entities with non-obvious relationships. The prompt's value lies in making design reasoning visible and contestable, not in producing a single 'correct' answer.

The ideal user has enough domain context to provide a bounded set of entities, relationships, access patterns, and non-functional requirements (e.g., latency budgets, consistency SLAs, read/write ratios). You must supply these as structured input; the prompt cannot invent them. The output is a decision tree where each branch explores a coherent design philosophy, and each leaf node evaluates that design against the provided constraints. This is not a prompt for greenfield projects where you're sketching an initial model from scratch—it assumes you already have a candidate logical model and need to reason about physical design trade-offs. The prompt works best when paired with a validation harness that checks for anti-patterns (e.g., missing indexes for query patterns, denormalization without a consistency strategy) before the output reaches a human reviewer.

Do not use this prompt for trivial schemas with fewer than five entities, for schemas already rigidly locked by an ORM or framework convention, or when the real constraint is organizational (e.g., 'the DBA team only allows third normal form') rather than technical. In those cases, the branching exploration adds noise without value. Also avoid this prompt when you need a single executable DDL script as the primary output—this prompt produces a comparison document, not a build artifact. If you need a production-ready schema, use this prompt first to select the strategy, then follow up with a separate generation prompt that produces the DDL. Always route the final output through human review, especially when the chosen design introduces eventual consistency or denormalization that downstream systems must accommodate.

This prompt template is designed to be pasted directly into your orchestration layer. It instructs the model to act as a data architect and generate a tree-of-thought analysis, exploring multiple normalization strategies, access patterns, and consistency models for a given domain. The output is a structured comparison of schema designs, not a single recommendation, making the trade-offs explicit for human decision-makers.

textCopy
You are a principal data architect evaluating schema designs for a new application. Your task is to perform a Tree-of-Thought analysis to compare different data modeling strategies. You must reason through multiple branches, evaluate trade-offs, and present a structured comparison.

## DOMAIN AND REQUIREMENTS
[DOMAIN_DESCRIPTION]
[ACCESS_PATTERNS]
[CONSISTENCY_REQUIREMENTS]
[SCALE_AND_PERFORMANCE_CONSTRAINTS]

## REASONING PROCESS
For each of the following strategies, generate a distinct branch of thought. For each branch, you must:
1.  **Propose a Schema:** Briefly describe the core entities, relationships, and data types.
2.  **Analyze Trade-offs:** Evaluate the strategy against the requirements, explicitly stating pros and cons for write performance, read performance, consistency guarantees, and query complexity.
3.  **Identify Anti-Patterns:** Flag any potential design pitfalls, such as unbounded lists, hot partitions, or impedance mismatches.
4.  **Score Confidence:** Assign a confidence score (0.0 to 1.0) for this strategy's suitability.

## STRATEGIES TO EXPLORE
- **Branch A: Full Normalization (3NF)**
- **Branch B: Denormalized for Read-Heavy Access**
- **Branch C: Event Sourcing Pattern**
- **Branch D: CQRS with Materialized Views**

## OUTPUT FORMAT
Provide the analysis as a JSON object with the following structure. Do not include any text outside the JSON.
{
  "domain_summary": "A one-sentence summary of the domain.",
  "branches": [
    {
      "strategy_name": "Full Normalization (3NF)",
      "proposed_schema": "A brief description of the schema.",
      "trade_off_analysis": {
        "pros": ["Pro 1", "Pro 2"],
        "cons": ["Con 1", "Con 2"]
      },
      "anti_patterns_identified": ["Pattern 1"],
      "confidence_score": 0.85
    }
    // ... other branches
  ],
  "recommended_path": "A final recommendation that synthesizes the best aspects of the top branches, or a clear statement that the choice depends on a specific, unresolved constraint."
}

To adapt this template, replace the square-bracket placeholders with concrete details about your application. The [DOMAIN_DESCRIPTION] should be a rich paragraph explaining the business context. [ACCESS_PATTERNS] must detail specific queries, their frequency, and latency requirements. Be precise in [CONSISTENCY_REQUIREMENTS]—state whether strong, eventual, or causal consistency is needed for which operations. The model's confidence scores are a useful signal, but for high-stakes schema decisions, the output must be reviewed by a human data architect. Use the anti_patterns_identified field as a checklist for your own design review.

This prompt is designed to be used as a synchronous reasoning step within a larger data modeling workflow, not as a standalone chat interaction. The typical integration pattern places this prompt after a requirements gathering phase and before a schema generation phase. The application should collect the [DATA_REQUIREMENTS], [ACCESS_PATTERNS], [CONSISTENCY_REQUIREMENTS], and [CONSTRAINTS] from upstream inputs—such as a PRD parser, a stakeholder interview summary, or an API specification—and inject them into the prompt template. The model's output is a structured tree of design branches, each with a normalization strategy, consistency model, and trade-off analysis. This output is then parsed by the application and passed to a downstream prompt or deterministic code that generates the actual DDL, ORM models, or schema migration files.

Validation and retry logic is critical because the prompt produces comparative analysis, not a single correct answer. Implement a two-stage validation harness. First, validate the output structure: parse the JSON to confirm each branch contains the required fields (strategy_name, normalization_level, consistency_model, access_pattern_fit, trade_offs, anti_pattern_risks). If parsing fails, retry with a stricter [OUTPUT_SCHEMA] and an explicit instruction to fix the malformed JSON. Second, validate the content against the input constraints: check that each branch's consistency_model does not violate the [CONSISTENCY_REQUIREMENTS] and that the access_pattern_fit addresses every pattern listed in [ACCESS_PATTERNS]. Branches that fail constraint validation should be flagged for human review rather than silently dropped. For high-stakes schema decisions (e.g., financial ledgers, healthcare records), route the full tree output to a human reviewer queue before any DDL generation occurs.

Model selection and tool use should match the complexity of the design space. For simple schemas with fewer than 5 entities, a fast model like claude-3-haiku or gpt-4o-mini with a low max_tokens setting is sufficient. For complex domains with 10+ entities, multiple consistency boundaries, and cross-service access patterns, use a frontier model like claude-3-opus or gpt-4o with a higher reasoning budget. If your application has access to an existing data catalog or schema registry, wire it in as a tool: before the prompt runs, retrieve the current schema state and inject it into [CONTEXT] so the model can flag conflicts with existing tables. After the prompt runs, log the full input, output, and validation results to your prompt observability system. This trace is essential for debugging when a generated schema underperforms in production—you'll need to know which branch was selected and why. Avoid using this prompt in a tight loop; the branching exploration is token-intensive, so cache results when the same requirements appear repeatedly and only re-run when inputs change materially.