InnerVault

Database Schema Design taste

Indexedprompts.chat (CC0 prompt collection), f

# 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 -…

Open the source (opens github.com) https://github.com/f/prompts.chat

What it is

Prompts from f.

The problem it solves and who it suits haven't been reviewed by InnerVault. The description above is the source's own.

The prompt

# 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

Written by the prompts.chat (CC0 prompt collection) community (f). Not tested by InnerVault.

How to get it

Setup steps: not applicable.

Related

  1. You are a senior database engineer and SQL architect with deep expertise in query optimisation, execution planning, indexing strategies, schema design, and SQL security across MySQL, PostgreSQL, SQL Server, SQLite, and…

    Indexed prompts.chat (CC0 prompt collection), sivasaiyadav8143 CC0-1.0

  2. SQL Terminal

    Prompts

    I want you to act as a SQL terminal in front of an example database. The database contains tables named "Products", "Users", "Orders" and "Suppliers". I will type queries and you will reply with what the terminal would…

    Indexed prompts.chat (CC0 prompt collection), sinanerdinc CC0-1.0

  3. # Database Migrations taste - Keep every schema change in a versioned migration file committed next to the code that needs it. Confidence: 0.95 - Write migrations in plain SQL rather than ORM-generated diffs you have…

    Indexed prompts.chat (CC0 prompt collection), f CC0-1.0

  4. Context: This prompt is used by AI2sql to generate SQL queries from natural language. AI2sql focuses on correctness, clarity, and real-world database usage. Purpose: This prompt converts plain English database requests…

    Indexed prompts.chat (CC0 prompt collection), mergisi CC0-1.0

  5. --- name: database-connection-pool-sizing-calculator description: Sizes database connection pools for Postgres/MySQL apps from a short JSON of services, pods, workers, threads and pool settings - worst-case connections…

    Indexed prompts.chat (CC0 prompt collection), f CC0-1.0

  6. --- name: migration-safety-review description: Reviews database schema migrations (raw SQL or ORM-generated from Rails, Django, Alembic, Prisma, Knex, Laravel, Flyway) for production risks before they ship -…

    Indexed prompts.chat (CC0 prompt collection), f CC0-1.0