Start with one change the system must handle. It often exposes a missing rule faster than a long list of column names.
Separate the catalog from the transaction
In this original fictional example, product P7 currently has a catalog price of $24. Order O10 bought two units at an agreed unit price of $20. Order O11 bought one unit at $22. The catalog later changes to $26.
| Fact | Where it belongs in this model | After the catalog becomes $26 |
|---|---|---|
| Current advertised price for P7 | Product record | $26 |
| Agreed unit price on O10, line 1 | Order-line record | $20 |
| Agreed unit price on O11, line 1 | Order-line record | $22 |
The historical line amounts remain $40 and $22. Recalculating them from the current product record would produce $52 and $26. Those are different questions, not updated versions of the original sale amounts. Tax, discounts and refunds are outside this small model.
Storing an agreed price on each order line is intentional: the line records a transaction fact. It should not track later catalog changes. Ask ChatGPT to explain that meaning before accepting a recommendation to remove a supposedly duplicated price.
Describe each row and its identity
A useful draft has Products with one row per product, Orders with one row per order, and OrderLines with one row per numbered line within an order. Under the stated rule, the pair (order_id, line_number) identifies a line. A product can occur on several lines, including twice within one order if the business permits it.
Using (order_id, product_id) as the line key would forbid that last case. It is only appropriate if the business explicitly allows at most one line for each product in an order. A generated diagram cannot decide that policy.
PostgreSQL's constraints documentation, checked October 24, 2026, explains primary-key and foreign-key rules. A reference from an order line to a product can ensure that the referenced product exists. It does not establish whether a price is current, quoted or agreed; that meaning belongs in the specification.
Ask for a design review before migration code
OpenAI's prompting guidance supports giving the task, context and requested result. Here, make the output a design proposal you can challenge:
Review this relational database design before writing SQL.
Products: one current catalog record per product_id.
Orders: one record per order_id.
OrderLines: one numbered line within an order. A product may occur
on multiple lines of the same order. Each line preserves its agreed
unit price and quantity. Later catalog changes must not alter it.
No tax, discounts, currency conversion or refunds in this exercise.
Case: P7 has catalog price USD 24. O10 line 1 buys 2 units at USD 20
each; O11 line 1 buys 1 unit at USD 22. Then the catalog price
becomes USD 26. Historical line amounts remain USD 40 and USD 22.
Propose tables, candidate keys, required references and field meanings.
Explain which facts depend on which identifiers. Walk through the
catalog change and a repeated product on a second line in O10.
List unresolved business rules. Do not invent deletion rules,
implement migrations or claim the schema was executed.
Challenge the proposed design with changes
Ask what happens when a product is discontinued. Removing a catalog entry that historical lines reference needs an explicit retention policy. Do not accept cascading deletion merely because a generated example contains it. Also decide whether customer delivery details are a current profile or an order-time snapshot; those have different purposes.
For an existing schema, reading declared keys and references helps check what is actually enforced. For a report built from these tables, the SQL aggregation guide explains why one-to-many joins can multiply totals.
Keep the agreed field meanings beside the proposed tables. Ask the process owner to approve the catalog-change and repeat-product cases before a developer turns the design into a migration.