Database Schema Design taste — ChatGPT prompt for relational schemas
The Database Schema Design taste ChatGPT prompt sets rules for designing relational database schemas. It covers keys, constraints, indexes, and data storage.
CC0-1.0 ★ 178,201 updated 2026-10-24
Open on GitHubWhat Database Schema Design taste does
The prompt lays out practical rules for relational schemas: use surrogate primary keys, enforce natural keys separately, and declare foreign keys in the database. It favors plural snake_case names for tables and singular ones for columns. Columns should default to NOT NULL unless an unknown value differs meaningfully from an empty one.
For time, it specifies UTC timestamptz; for money, integer minor units or numeric paired with a currency column. Denormalization should follow a measured read path, with its purpose and sync method documented. Indexes should come from real query patterns and EXPLAIN output. Schema changes belong in version-controlled migrations alongside the code that depends on them.
What it helps with
- design keys and constraints for relational tables
- choose storage rules for timestamps and money
- model many-to-many links with a join table
- decide where indexes or denormalization belong
Example prompts for the agent
- Design a relational schema for orders and order items using these rules.
- Review the users and roles tables and suggest keys, constraints, and indexes.
- How should this schema store payment amounts and timestamps?
How to install Database Schema Design taste
How to use
Paste the text at the start of a chat or into project instructions
Manually: put the files in place
.claude/commands/prompts.csv.mdQuestions about Database Schema Design taste
How do I install Database Schema Design taste in Claude Code?
Add the rules file at .claude/commands/prompts.csv.md.
What does Database Schema Design taste do?
It sets design rules for relational schemas, covering keys, database constraints, indexes, and migrations.
How does the prompt recommend storing money?
Use integer minor units or numeric with a separate currency column. It rules out floating-point values for money.
Source text
prompts.csv · CC0-1.0
# Database Schema Design taste of a pragmatic relational modeler - Use surrogate primary keys (`bigint` identity or UUIDv7) on every table and enforce natural keys with separate unique constraints. Confidence: 0.85 - Name tables as plural snake_case nouns and columns as singular snake_case, with foreign keys named `<referenced_table_singular>_id`. Confidence: 0.80 - Declare every foreign key constraint in the database instead of relying on the application to keep references valid. Confidence: 0.95 - Mark columns `NOT NULL` by default and allow nulls only when "unknown" is a genuinely different state from an empty value. Confidence: 0.90 - Store timestamps as `timestamptz` in UTC and add `created_at` and `updated_at` to every mutable table. Confidence: 0.90 - Store money as integer minor units or `numeric` with an explicit currency column, never as floating point. Confidence: 0.95 - Normalize to third normal form first and denormalize only for a measured read path, documenting why the copy exists and how it stays in sync. Confidence: 0.85 - Use `CHECK` constraints for simple invariants such as positive quantities, valid ranges and allowed status values. Confidence: 0.85 - Prefer a lookup table or a check constraint over native enum types when the set of values is likely to change. Confidence: 0.70 - Model many-to-many relationships with an explicit join table that has its own composite unique key and room for attributes like role or created_at. Confidence: 0.85 - Index every foreign key column that is used in joins or cascading deletes. Confidence: 0.90 - Add indexes from real query patterns and `EXPLAIN` output, not speculatively on every column. Confidence: 0.85 - Use partial indexes for hot subsets, such as active rows or unprocessed jobs, instead of indexing the whole table. Confidence: 0.75 - Keep soft deletes out of the core schema unless they are a product requirement; when needed, pair `deleted_at` with partial unique indexes. Confidence: 0.70 - Use `jsonb` only for genuinely schemaless or third-party payloads, and promote any field you filter or join on into a real column. Confidence: 0.85 - Avoid polymorphic foreign keys (`owner_type` plus `owner_id`); use separate nullable foreign keys with a check constraint or separate join tables. Confidence: 0.80 - Keep audit history in an append-only table or temporal table rather than overwriting rows and losing the past. Confidence: 0.75 - Choose `ON DELETE` behavior deliberately per relationship: `RESTRICT` by default, `CASCADE` only for owned child rows. Confidence: 0.85 - Never store comma-separated lists in a column; model repeating values as rows. Confidence: 0.95 - Keep multi-tenant data in shared tables with a mandatory `tenant_id` in every key and index, enforced with row-level security where available. Confidence: 0.75 - Review every schema change as a migration in version control together with the code that depends on it. Confidence: 0.90 - Document each table and non-obvious column with `COMMENT ON` so the schema explains itself in any database client. Confidence: 0.65
© f · CC0-1.0 · f/prompts.chat