Design a normalized database schema from plain requirements

Schema decisions are expensive to reverse, so getting the first design close to right matters. This prompt turns plain-language requirements into a normalized relational schema—aiming for third normal form while flagging any deliberate denormalization—and expresses it as real CREATE TABLE statements for your specific engine. It models relationships explicitly, explains how it handled many-to-many cases with join tables, and recommends indexes based on the query patterns you describe. The output's trade-off notes and open-questions list are where it earns its keep: schema design is full of judgment calls (soft deletes vs. hard, UUID vs. serial, where to denormalize for read speed), and surfacing them lets you decide deliberately. Give it the database engine, because type systems and index features differ between Postgres, MySQL, and SQLite, and describe your most important query patterns so indexing advice is grounded. Use it as a design partner for a first draft you then refine—not as a final migration you apply blindly.

What you can expect back

```sql
CREATE TABLE categories (
  id        BIGSERIAL PRIMARY KEY,
  name      TEXT NOT NULL UNIQUE
);
CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  category_id BIGINT NOT NULL REFERENCES categories(id),
  name        TEXT NOT NULL,
  price_cents INT  NOT NULL CHECK (price_cents >= 0)
);
CREATE INDEX idx_products_category ON products(category_id);
CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);
CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);
```
ERD: users 1—N orders; orders N—M products via order_items; categories 1—N products.

Open questions:
- Store price on order_items to freeze historical price? (Recommended—prices change.)
- Soft deletes for products/users?
- Is one-category-per-product permanent, or will tagging be needed later?

Illustrative example — your results will vary by tool and inputs.

Get sharper results

• 01Always name the engine—Postgres TIMESTAMPTZ, MySQL ENUM, and SQLite's loose typing change the DDL.
• 02Act on the 'open questions'—decisions like freezing order prices are easy now, painful later.
• 03Give real query patterns so index suggestions match your access patterns, not generic guesses.
• 04Review every foreign key's ON DELETE behavior before applying; the model may omit cascade rules.

Adapt it for your case