A Repeatable Modeling Workflow and First Case Study

Learning outcomes

This lesson closes Chapter 1 by turning the previous ideas into a repeatable workflow. You will take a small WorkshopHub requirements set from domain language to a first conceptual model and then to a starter relational schema. Later chapters will revisit and improve this design as you learn keys, relationships, normalization, transactions, indexes, workload analysis, and evolution.

The modeling loop

Real data modeling is iterative. You rarely move through the steps exactly once. New questions appear when you draw relationships; normalization reveals hidden dependencies; query analysis reveals missing historical facts. A useful default loop is:

1. Define scope and goals.
2. Build a domain glossary.
3. Collect data requirements, examples, and reports.
4. Identify candidate entities and their identities.
5. Identify relationships, cardinality, optionality, and history.
6. Record rules and invariants.
7. Create a conceptual model.
8. Map to a logical relational model.
9. Normalize and validate against requirements.
10. Study workloads and choose physical structures.
11. Review with domain experts and developers.
12. Implement, test with real scenarios, and evolve.

Case-study requirements

WorkshopHub needs a first operational database with the following agreed requirements:

• A customer can register multiple assets.
• An asset belongs to one current customer in version 1.
• An asset is identified internally and also has a manufacturer plus serial number.
• A work order is opened for exactly one asset.
• A work order can have zero or more technician assignments over time.
• Assignments record start time, optional end time, and role.
• A work order can consume many parts; quantity and charged unit price must be preserved at the time of use.
• Operations needs queues by status and opening time.

Step 1: glossary before schema

Glossaries prevent a subtle source of defects: two teams using the same word with different meanings.

Step 2: conceptual model

The two associative concepts—assignment and part usage—are not merely “join tables.” They carry their own facts. Assignment has time and role. Part usage has quantity and charged unit price. This is an important modeling pattern you will revisit in Chapter 3.

Step 3: starter logical schema

Customer(  customer_id PK,  full_name,  email)Asset(  asset_id PK,  customer_id FK -> Customer,  manufacturer,  serial_number,  UNIQUE(manufacturer, serial_number))WorkOrder(  work_order_id PK,  asset_id FK -> Asset,  status,  opened_at,  closed_at)Technician(  technician_id PK,  full_name)WorkOrderAssignment(  work_order_id FK -> WorkOrder,  technician_id FK -> Technician,  started_at,  ended_at,  role)Part(  part_id PK,  sku UNIQUE,  description)PartUsage(  work_order_id FK -> WorkOrder,  part_id FK -> Part,  quantity,  charged_unit_price)

This is intentionally not the final schema. We have not yet resolved every key choice, normalization question, temporal rule, or physical access path.

Step 4: a runnable SQLite prototype

A prototype turns modeling decisions into executable constraints. The following subset is enough to test several important rules:

PRAGMA foreign_keys = ON;CREATE TABLE customer (    customer_id INTEGER PRIMARY KEY,    full_name   TEXT NOT NULL,    email       TEXT NOT NULL UNIQUE);CREATE TABLE asset (    asset_id      INTEGER PRIMARY KEY,    customer_id   INTEGER NOT NULL,    manufacturer  TEXT NOT NULL,    serial_number TEXT NOT NULL,    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),    UNIQUE (manufacturer, serial_number));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','cancelled')),    opened_at     TEXT NOT NULL,    closed_at     TEXT,    FOREIGN KEY (asset_id) REFERENCES asset(asset_id));CREATE TABLE technician (    technician_id INTEGER PRIMARY KEY,    full_name     TEXT NOT NULL);CREATE TABLE work_order_assignment (    work_order_id  INTEGER NOT NULL,    technician_id  INTEGER NOT NULL,    started_at     TEXT NOT NULL,    ended_at       TEXT,    role           TEXT NOT NULL,    PRIMARY KEY (work_order_id, technician_id, started_at),    FOREIGN KEY (work_order_id) REFERENCES work_order(work_order_id),    FOREIGN KEY (technician_id) REFERENCES technician(technician_id),    CHECK (ended_at IS NULL OR ended_at >= started_at));

Step 5: validate with scenarios, not only diagrams

Try to represent normal cases, edge cases, and forbidden cases.

Step 6: record open questions

A first model should end with a list of questions, not with false certainty.

• Can multiple technicians be actively assigned at the same time?
• Should technician role be free text or controlled reference data?
• Do we need ownership history for assets?
• Can a work order be reopened after closure?
• Does part usage need technician/task attribution?
• Which queries must remain fast when the database contains millions of work orders?

Each question previews later chapters. Cardinality and associative entities appear in Chapters 2–5, normalization in Chapters 7–9, transactions in Chapter 10, indexes and workload design in Chapters 11–12, history in Chapter 14, and evolution in Chapter 16.

Chapter checkpoint

Summary and next chapter

You now have a complete beginner workflow: define scope, establish vocabulary, extract requirements, expose assumptions, discover concepts and relationships, state invariants, sketch a conceptual model, map it logically, prototype constraints, and validate with scenarios. Chapter 2 begins the deeper design work by teaching how to discover entities, attributes, identifiers, and special entity types precisely.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Thomas Connolly and Carolyn Begg, Database Systems.
• C. J. Date, Database Design and Relational Theory.
• Peter P. Chen, “The Entity-Relationship Model—Toward a Unified View of Data,” 1976.