Data Modeling & Schema Design
Conceptual, Logical, and Physical Models
Database design moves from business meaning, through a technology-neutral logical structure, to a system-specific physical implementation. Keeping those decisions distinct preserves meaning while allowing storage and access paths to evolve.
- Requirements describe facts, rules, and questions before they describe tables.
- A conceptual model names the enterprise concepts and their relationships.
- A logical model maps concepts to relations, attributes, keys, and constraints.
- A physical model chooses implementation details for a measured workload.
- The process is iterative: transaction needs can reveal a missing concept, while normalization can reveal a conceptual modeling error.
- Logical changes usually reach more application code than physical changes, so meaning and constraints deserve the earliest review.
One requirement at three levels
| Level | Question | Commerce decision |
|---|---|---|
| Conceptual | Which facts and rules matter? | A customer places orders; each order contains one or more product lines |
| Logical | How are facts and constraints represented? | orders.customer_id references customers; order_items is identified within an order |
| Physical | How will this workload be stored and found? | Index orders(customer_id, placed_at); consider time partitioning only after measurement |
A useful baseline is customers(customer_id, email, name), orders(order_id, customer_id, placed_at, status), products(product_id, sku, name, current_price), and order_items(order_id, line_no, product_id, quantity, unit_price). The logical model records unit_price on the line because it is the price agreed for that transaction, not a redundant copy of the product's current price.