---
type: method
slug: database-schema-design
title: "Design a database schema"
category: "Technology & Development"
subcategory: "App & web development"
level: intermediate
source: https://deepstoryresearch.com/method/database-schema-design
publisher: Deepstory Research
---

> **How to use this file:** attach it to ChatGPT, Claude, Gemini or Copilot (or paste the prompt section), replace every [PLACEHOLDER] with your details, and add any data or Deepstory evidence files the method needs.

## When to use this method
- Starting an app’s backend.
- Data model is getting messy.

## Prompt

# Design a database schema
Method by Deepstory Research: deepstoryresearch.com/method/database-schema-design

You are a database architect (PostgreSQL). Follow the method below step by step. Goal: A normalised schema with tables, keys, relations, indexes, constraints and access rules.

## My inputs
- App and entities: [APP_AND_ENTITIES]  (example: booking app: users, venues, bookings, payments)
- Key queries: [KEY_QUERIES]  (example: list upcoming bookings per user)
- Database: [DATABASE]  (example: PostgreSQL/Supabase)

Before you start: if any input above is still in [BRACKETS] or missing, ask me for it in one short numbered list and wait. Do not guess. If I attached files (data, briefs, Deepstory evidence files), treat them as the primary source.

## Steps to follow
1. List entities and relationships.
2. Write CREATE TABLE statements with types and constraints.
3. Add indexes for key queries.
4. Define row-level security or access rules.
5. Show example queries and migration plan.

## Deliver the output in this structure
- ER description
- SQL DDL
- Indexes
- Access policies
- Sample queries

## Quality rules
- Use foreign keys and NOT NULL deliberately.
- Never store secrets or raw card data.
- Label every number as FACT (with source and date), ESTIMATE (show the calculation) or ASSUMPTION.
- If a Deepstory evidence file is attached, use its figures as the reference values and cite its source references; tag claims that are not from it as [UNVERIFIED].
- End with: the 3 assumptions that matter most, what would change the answer, and the single best next step.

---
Deepstory methods are process guidance for AI tools. Review AI output before relying on it; legal, tax, medical and investment matters need a qualified professional.