wowprompt

Database schema design from plain feature requirements

Turns a feature description into tables, columns, types, keys and indexes, with the reasoning and the SQL to create them. Use it when starting a new app or adding a feature that needs new data.

A table list with columns, types and constraints, a relationship summary, an index plan and runnable CREATE TABLE statements
The output this prompt produced, unedited.

The prompt

Fill in these first

  • Database: [MySQL 8, PostgreSQL 16, SQLite, SQL Server]
  • What the app does: [two or three sentences]
  • Things to store: [e.g. users, orders, products, reviews]
  • Rules: [e.g. a user can review a product once; orders keep the price paid]
  • Expected scale: [rows per table in a year]
  • Most common reads: [e.g. list a user's orders newest first]

Task

Design a schema that stores this data correctly, keeps it consistent and serves the common reads quickly.

Output format

  1. Entity summary: each table and one line on what a row represents.
  2. Table definitions, one block per table:

| Column | Type | Null allowed | Default | Constraint | Notes |

  1. Relationships: one line per foreign key, with the delete behaviour and why.
  2. Index plan: each index, its columns and the read it serves.
  3. SQL: complete CREATE TABLE and CREATE INDEX statements for the stated database.
  4. Trade-offs: any deliberate denormalisation and what keeps it in sync.
  5. Open questions: decisions that need the product owner.

Quality rules

  • Every table has a primary key.
  • Store money as integer minor units or a fixed decimal, never a float.
  • Store timestamps in UTC with a clear type.
  • Use unique constraints to enforce the business rules given, not only application code.
  • Name tables and columns consistently in snake_case.
  • Do not store passwords; store a password hash column only.
  • No index without a stated read it supports.

Example

| order_total | BIGINT | no | 0 | CHECK >= 0 | amount in paise or cents |

database schema designdatabase designcreate table sqldata modelingtable relationships

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity