Chapter 01 · From Requirements to Data Models
Conceptual, Logical, and Physical Data Models
Understand conceptual, logical, and physical data models as different levels of abstraction, each answering a different class of design questions.
Learning outcomes
The same system can be modeled at several levels. A conceptual model communicates business meaning, a logical model specifies data structures independently of a particular product, and a physical model chooses implementation details for a real database and workload. Confusing these levels causes premature optimization and hard-to-review diagrams.
Explain the purpose of conceptual, logical, and physical data models.
Identify which design questions belong at each level.
Trace one WorkshopHub concept through all three models.
Avoid vendor-specific choices before the logical design is stable.
Conceptual model: what exists and how it relates
The conceptual model is intentionally close to domain language. It normally shows major entity types and relationships while avoiding column types, indexes, partitioning, and other implementation details.
| |
This picture already asks valuable questions: Is assignment truly an entity with its own facts? Can a work order exist before a technician is assigned? Does part usage belong directly to the order or to a specific repair task?
Logical model: exact structures without vendor details
The logical model adds identifiers, attributes, cardinality, optionality, keys, and normalization decisions. It is specific enough to map to a relational schema, but should remain largely independent of PostgreSQL, MySQL, SQL Server, or another product.
| Logical relation | Identifier | Important attributes |
|---|---|---|
| Customer | customer_id | full_name, email |
| Asset | asset_id | manufacturer_id, serial_number, customer_id |
| WorkOrder | work_order_id | asset_id, status, opened_at, closed_at |
| WorkOrderAssignment | assignment_id or composite key | work_order_id, technician_id, role, started_at, ended_at |
At this stage you can reason about candidate keys and dependencies. You still do not need to decide whether identifiers are BIGINT, UUIDs, sequences, or application-generated values.
Physical model: how this engine will store and access it
The physical model makes engine- and workload-specific decisions. It includes concrete types, indexes, partitioning, generated columns, storage options, clustering, and sometimes denormalization justified by measurements.
CREATE INDEX idx_work_order_open_queueON work_order(status, opened_at);CREATE INDEX idx_assignment_active_orderON work_order_assignment(work_order_id)WHERE ended_at IS NULL;These indexes belong to physical design because their value depends on expected queries, data distribution, and database capabilities. Another engine may require a different implementation.
One fact through three levels
Requirement: “A work order can have multiple technician assignments over time, and the current assignment must be found quickly.”
| Level | Representation |
|---|---|
| Conceptual | WorkOrder has Technician Assignments; assignment has time. |
| Logical | WorkOrderAssignment(work_order_id, technician_id, started_at, ended_at, role). |
| Physical | Concrete column types plus an index that accelerates active assignments. |
Do not solve physical problems in the conceptual model
A conceptual review with a domain expert should not be dominated by B-tree order or UUID storage. Likewise, a production design cannot stop at a conceptual picture; it must eventually address indexes, transaction boundaries, migration strategy, and operational behavior.
Ask “What decision are we making right now?” If the answer is about meaning, stay conceptual. If it is about normalized structures and keys, work logically. If it is about a specific engine and workload, work physically.
Compare three common mistakes
Premature physical design
The team debates index types before agreeing whether assignments need history. This optimizes a structure that may be conceptually wrong.
Logical model with hidden business rules
Tables look normalized, but cardinality and optionality are undocumented. Developers infer different rules.
Conceptual model treated as deployable schema
The diagram communicates well, but no one decides data types, constraints, transaction handling, or access paths. Production problems are postponed rather than solved.
Practice: classify the decision
- “An asset may have many work orders.”
- “WorkOrder references Asset through a foreign-key attribute.”
- “Use a partial index for active technician assignments.”
- “Manufacturer + serial_number forms a candidate key.”
- “Use PostgreSQL
timestamptzfor assignment times.”
Review the answers
The first is conceptual. The second and fourth are logical. The third and fifth are physical because they depend on implementation capabilities and choices.
Summary and next lesson
Conceptual, logical, and physical models are not competing diagram styles; they are layers of decision-making. Moving deliberately from meaning to structure to implementation keeps stakeholder review clear and postpones engine-specific tradeoffs until they can be evaluated against real requirements. The next lesson puts the chapter together as a repeatable modeling workflow.
References
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
- Thomas Connolly and Carolyn Begg, Database Systems: A Practical Approach to Design, Implementation, and Management.
- Peter P. Chen, “The Entity-Relationship Model—Toward a Unified View of Data,” 1976.