Design a database schema

This prompt designs a database schema for an application, given the database and the main entities.

text-database-schema-design — chatgpt — 53 words

Design a database schema for [APPLICATION].

Database: [POSTGRESQL / MYSQL / OTHER]
Main entities and rules: [DESCRIBE]
Most common queries: [QUERIES]
Expected size: [SCALE]

Provide the tables with columns, types, primary and foreign keys, indexes, constraints and a short explanation of key design choices. Point out any trade-offs and future changes to consider.

Edit in the builder Copy & open ChatGPT ↗Copy & open Gemini ↗Copy & open Claude ↗

Describe your top five queries up front so the schema is built for how you will use it.

Get a better result

What you will get

A schema for your app with tables, columns and types, primary and foreign keys, indexes and constraints, short reasons for the main choices and the trade-offs and future changes to think about.

Why it works

The common queries and expected size are the two lines that turn a textbook schema into one built for your app. Naming the database matters because types and features differ between PostgreSQL and MySQL. Asking for reasons and trade-offs lets you check the thinking, and the future changes note helps you avoid a design that is hard to change later.

Common mistake

Everything goes into one or two wide tables.

Fix: Ask it to list each thing in your app that has its own life, like a user, an order or a payment, and give each a table.

Also watch for

There are no constraints, only columns. Ask for NOT NULL, UNIQUE and CHECK rules for your business rules, such as “a booking ends after it starts”.
Deleting a user breaks or wipes too much. Ask what should happen to related rows when a parent row is deleted, and to pick cascade, restrict or soft delete per table.

Try a variation: add history

Ask how to keep a record of changes, such as price history or status changes, without slowing the main tables.

Still not right? Reply with this

Write my five main queries against this schema and point out any that need an extra index or a design change.