Chapter 01 · From Requirements to Data Models
What Data Modeling Is and Why Database Design Matters
Learn what a data model represents, how database design connects business meaning to stored structures, and why design errors become expensive later.
Learning outcomes
Database design begins before CREATE TABLE. The designer first decides what the system must remember, which facts belong together, which relationships matter, and which states must never be allowed. By the end of this lesson, you should be able to explain why a data model is a representation of a domain rather than a picture of tables.
Define data modeling, database design, schema, and data architecture at a beginner-friendly but precise level.
Explain how poor modeling creates duplication, ambiguity, inconsistent rules, and expensive application logic.
Separate domain concepts from implementation choices such as table names, indexes, and vendor-specific types.
Identify the first modeling questions for a small service-management system.
What is a data model?
A data model is a disciplined representation of the facts a system needs to remember and the rules that make those facts meaningful. It answers questions such as: What kinds of things exist in the domain? What properties describe them? How are they related? Which facts identify one instance uniquely? Which values are optional? Which combinations are impossible?
For example, imagine a repair company called WorkshopHub. Customers register assets such as laptops or machines. A customer requests a repair, the company opens a work order, a technician performs work, and parts may be consumed. A beginner may immediately imagine five tables. A modeler first asks what each word means.
Meaning
What does “customer,” “asset,” “work order,” and “technician” mean in this organization?
Identity
How do we distinguish one customer, asset, order, or part from another?
Relationships
Can one asset have many work orders? Can one work order have several technicians?
Rules
May a closed work order be reassigned? Can a part quantity become negative?
A data model is not merely a storage diagram. It is an explicit statement of what the organization believes to be true about its data.
Data modeling versus database design
The terms overlap, but keeping them separate is useful. Data modeling focuses on semantics: entities, facts, relationships, identifiers, constraints, and business rules. Database design carries that model into an implementable schema and then makes physical choices for a specific workload and database engine.
| Question | Mostly modeling | Mostly database design |
|---|---|---|
| What is a work order? | Yes | Uses the answer |
| Can one work order contain many parts? | Yes | Maps it to keys/tables |
Should work_order_id be an integer or UUID? | Usually not yet | Yes |
Should an index begin with status or scheduled_at? | No | Yes, after studying queries |
| Must every work order belong to exactly one asset? | Yes | Enforces with constraints |
Why design mistakes become expensive
A weak model can still produce software that appears to work during a demo. The cost appears later when the system has real data, concurrent users, reports, integrations, and historical requirements. Consider a single table containing customer name, customer phone, asset serial number, technician name, five part columns, and the current work-order status. It is quick to create, but it mixes several independent facts.
- A customer phone change must be repeated across many work orders.
- A technician rename or correction touches historical rows unnecessarily.
- Adding a sixth part requires a schema change if parts are stored as
part_1throughpart_5. - Deleting the only work order for an asset may accidentally erase the only stored record of that asset.
- Different rows may spell the same status differently:
Closed,closed,DONE.
These are not simply SQL problems. They are symptoms that the stored structure does not match the independent facts in the domain.
The schema is a contract
A database schema is an executable part of the system's contract. Column types, nullability, unique constraints, foreign keys, check constraints, and transaction boundaries can reject invalid states before those states spread to reports or downstream services.
CREATE TABLE work_order ( work_order_id INTEGER PRIMARY KEY, asset_id INTEGER NOT NULL, status TEXT NOT NULL CHECK (status IN ('open', 'scheduled', 'in_progress', 'closed')), opened_at TEXT NOT NULL, closed_at TEXT, FOREIGN KEY (asset_id) REFERENCES asset(asset_id), CHECK (closed_at IS NULL OR status = 'closed'));This code is only a possible implementation. The important modeling decisions came earlier: a work order belongs to an asset; status has a controlled meaning; closure time is related to closure state.
Start with nouns—but do not stop there
A common beginner technique is to underline nouns in a requirements document. Nouns can reveal candidate entities, but not every noun deserves a table. “Address” might be an attribute, a reusable entity, or a historical record depending on the business. “Status” may be a controlled value, a reference table, or an event history. Modeling therefore combines vocabulary discovery with questions about identity, lifecycle, reuse, and rules.
Mini exercise
Read the statement: “A customer can register several assets. Each repair request opens a work order for one asset. A technician may work on many orders, and an order can consume many parts.”
- List candidate entities.
- Circle verbs that imply relationships.
- Write one question whose answer would change the model.
Review one possible answer
Candidate entities include Customer, Asset, WorkOrder, Technician, and Part. Relationship verbs include register, opens, work on, and consume. A high-value question is whether a work order can have more than one technician; the answer changes cardinality and possibly introduces an assignment entity.
Design quality is judged against requirements
There is no universally perfect schema. A design is good when it represents the required facts accurately, enforces important invariants, supports expected workloads, remains understandable, and can evolve safely. Two systems in the same industry may need different models because their policies differ.
For example, one repair business may assign exactly one technician to each work order. Another may have teams, handoffs, and time tracking. The first can store technician_id on the work order. The second likely needs a work_order_assignment table with assignment times and roles. Neither design is automatically superior; each should match its rules.
Checkpoint and practice
Concept check
- Why is an ER diagram not the same thing as the database?
- Give one example of a business rule that should influence schema constraints.
- Why should index design normally come after workload discovery?
- What makes a candidate entity more than just a noun?
Review the answers
An ER diagram is a representation of concepts and relationships, while the database is the implemented storage system. Business rules such as “each asset has a unique serial number within a manufacturer” can become uniqueness constraints. Indexes are physical structures chosen for actual access patterns, so guessing them too early can add write cost without helping queries. A candidate entity usually has independent identity, meaningful attributes or relationships, and a lifecycle the system must track.
Summary and next lesson
Data modeling captures the domain's facts and rules; database design turns that meaning into an implementable, enforceable, workload-aware schema. Good design is not about drawing more boxes. It is about making ambiguity visible before ambiguity becomes data. The next lesson shows how to extract precise data requirements from ordinary business language.
References
- Peter P. Chen, “The Entity-Relationship Model—Toward a Unified View of Data,” 1976.
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
- C. J. Date, An Introduction to Database Systems.
- Abraham Silberschatz, Henry F. Korth, and S. Sudarshan, Database System Concepts.