Design a Normalized Database Schema

This prompt asks an AI acting as a database architect to design a normalized relational schema from a feature or system description, returning ready-to-run DDL plus the reasoning behind it. It's most useful at the start of a project or when adding a major feature, when you want a defensible first draft of tables, keys, and indexes rather than evolving the schema ad hoc. The explicit asks for indexes tied to query patterns and for flagged denormalization tradeoffs are what separate this from a naive table dump, forcing the model to justify its decisions.

What you can expect back

CREATE TABLE workspaces (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id BIGSERIAL PRIMARY KEY,
  workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active'
);

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  due_date DATE
);

CREATE TABLE task_assignees (
  task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL,
  PRIMARY KEY (task_id, user_id)
);

-- Indexes for common filters
CREATE INDEX idx_projects_workspace ON projects(workspace_id);
CREATE INDEX idx_tasks_project_due ON tasks(project_id, due_date);

Notes:
- task_assignees is a junction table to keep the many-to-many in 3NF.
- Denormalization tradeoff: caching a task_count on projects would speed dashboards but risks drift; left out for now.

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

Get sharper results

• 01Describe your highest-frequency queries (for example 'list overdue tasks per workspace') so the index recommendations target real access patterns instead of generic guesses.
• 02Ask the model to call out where it chose 3NF versus a deliberate denormalization, then decide per case rather than accepting either default blindly.
• 03Specify expected scale (thousands vs. billions of rows) because it changes choices like partitioning, surrogate key types, and whether to index foreign keys.
• 04Request ON DELETE behavior and uniqueness constraints explicitly, since these are easy for a first draft to omit and painful to retrofit later.
• 05Have it generate a quick ERD description or Mermaid diagram alongside the DDL so non-DBAs on the team can review the relationships.

Adapt it for your case