Conceptual and Logical Model Review

Learning outcomes

A capstone model should be reviewed before implementation. The goal is not to admire the diagram; it is to challenge its meaning. Every entity, relationship, optionality marker, identifier, and temporal rule should trace back to a requirement.

Conceptual model sketch

AssetOwnership introduces time between Customer and Asset.

Review row grain

Entity boundary review

Ask whether any entity is merely an attribute group with no independent identity or lifecycle. Conversely, ask whether repeated facts are hidden inside columns or JSON and deserve their own entity.

Customer and Asset

Current relationship alone:

is insufficient if ownership history matters. Logical model:

with temporal non-overlap constraints.

WorkOrder and Technician

Many technicians can work on many WorkOrders over time. The associative entity WorkOrderAssignment stores:

role_codestarted_atended_atis_primary / role semantics

PartUsage is not a direct M:N shortcut

PartUsage carries facts:

quantitycharged_unit_pricerecorded_atrecorded_by

Therefore it is a first-class associative entity.

Optionality review

Questions:

• Can an Asset exist before owner assignment?
• Can WorkOrder exist before technician assignment?
• Can a WorkOrder have zero PartUsage?
• Can a DiagnosticCapture exist without a WorkOrder?

Optionality should reflect workflow, not drawing convenience.

Candidate keys

Tenant ownership

Decide whether tenant_id is:

• stored on every tenant-owned table;
• part of composite unique constraints;
• part of tenant-aware foreign keys;
• used in row-level security and indexes.

Temporal review

For AssetOwnership:

valid_from inclusivevalid_to exclusiveat most one active owner per assetbackdating allowed?future-dated ownership allowed?

These are model rules, not implementation details.

Status review

Separate:

WorkOrder.status_code        -- current stateWorkOrderStatusEvent         -- transition historyWorkOrderStatus              -- reference metadata

Each has a different role.

Reference data review

Candidate reference entities:

• WorkOrderStatus;
• AssignmentRole;
• FailureCategory;
• PartCategory;
• DiagnosticSeverity.

JSON boundary review

DiagnosticCapture.payload_json is acceptable because payloads vary by source. But promote:

captured_atsource_systemschema_versionseverity_code

into stable typed columns.

Naming review

Use consistent names:

customer_idwork_order_idopened_atclosed_atstatus_codevalid_fromvalid_to

Avoid synonyms such as customer/account/client unless they mean different concepts.

Diagram readability review

Create one conceptual diagram for business stakeholders and a separate logical diagram for engineering detail. Do not overload one diagram with every index, data type, and audit column.

Stakeholder walkthrough

Use scenarios:

1. Customer buys an Asset.
2. Asset changes owner.
3. WorkOrder is opened.
4. Two technicians are assigned.
5. Parts are consumed.
6. WorkOrder closes.
7. Auditor asks who changed status.

If the model cannot narrate these scenarios clearly, it is not ready.

Model-review checklist

• Every entity has one clear grain.
• Every relationship has named semantics.
• Cardinality and optionality are explicit.
• Business keys are identified.
• Temporal facts are not overwritten accidentally.
• Tenant boundaries are visible.
• JSON does not hide core relations.
• Reference data has ownership.

Checkpoint

Summary and next lesson

A logical model is ready when its grain, keys, cardinalities, time semantics, and ownership rules survive stakeholder scenarios and engineering scrutiny. The next lesson normalizes and implements this model as a relational schema.

References

• Course Chapters 2–5 for entity, relationship, notation, and relational mapping.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Thomas Connolly and Carolyn Begg, Database Systems.