Relational Schema Design AI Prompt

Design a normalized relational schema for this domain.

Domain: {{domain}}
Entities described: {{entities}}
Key relationships: {{relationships}}
Database: {{database}} (PostgreSQL, MySQL, SQL Server, Oracle)

1. Normalization levels:

   1NF (First Normal Form):
   - Atomic values: no repeating groups, no arrays in columns
   - Each column holds a single value
   - Example violation: storing 'tag1,tag2,tag3' in a tags column

   2NF (Second Normal Form):
   - Must be in 1NF
   - No partial dependencies: every non-key column depends on the WHOLE primary key
   - Applies to tables with composite primary keys

   3NF (Third Normal Form):
   - Must be in 2NF
   - No transitive dependencies: non-key columns should not depend on other non-key columns
   - Example violation: storing both zip_code and city in the same table (city depends on zip)

   BCNF (Boyce-Codd Normal Form):
   - Stricter version of 3NF; every determinant must be a candidate key
   - Required for mission-critical schemas

2. Primary key strategy:
   - Surrogate key: auto-incrementing integer or UUID — decouples business logic from identity
   - Natural key: use when the business key is stable and unique (e.g. ISO country code)
   - Composite key: for junction tables (order_id + product_id as PK in order_items)
   - UUID vs SERIAL: UUID is globally unique (good for distributed systems); SERIAL is faster for single-DB

3. Foreign key design:
   - Always create FK constraints: enforces referential integrity at the database level
   - ON DELETE behavior: RESTRICT (default, safest), CASCADE (auto-delete children), SET NULL
   - Index all FK columns: queries joining on FK columns need indexes

4. Column data types:
   - Prefer: TIMESTAMP WITH TIME ZONE (not WITHOUT), NUMERIC for money (not FLOAT), TEXT over VARCHAR(n) in Postgres
   - Avoid: storing dates as VARCHAR, using FLOAT for currency

5. Schema documentation:
   - Add comments to every table and column: COMMENT ON TABLE orders IS '...';
   - Maintain an ERD (Entity Relationship Diagram) in draw.io or dbdiagram.io

Return: normalized schema DDL, primary and foreign key definitions, index recommendations, and ERD description.