Lesson 7	Users revisited
Objective	Revise an ER diagram based on a final interview with end users.

Interview End Users (Final ERD Review)

Before you translate a conceptual ER diagram into a logical relational schema, hold a structured review with end users and key stakeholders. This meeting verifies that the model reflects business objects, business rules, and real queries/views. Use the findings to refine the logical schema and its normalization before physical implementation. Finding missing rules now can prevent later schema and application changes.

A “final interview” is a review checkpoint for the current model, not the end of user involvement. Revisit decisions when new requirements, exceptions, or implementation findings emerge.

Goals of the session

Business objects: Confirm that entities and attributes match the language users actually use (nouns and properties in their workflows).
Business rules: Validate constraints: cardinality, optional vs. mandatory participation, identifying vs. non-identifying relationships, value domains, and lifecycle rules.
User queries/views: Confirm that the model represents the information and relationships needed for priority reports, key performance indicators (KPIs), searches, and critical transactions. Query speed must be evaluated later with an implemented schema, representative data, and workload tests.

Who to invite (and why)

Process owners / subject-matter experts: Explain business rules and resolve conflicting requirements.
Representative everyday users: Describe routine work, exceptions, and workarounds across relevant roles or teams.
Power users / analysts: Own complex queries and reports.
Application developers: Validate integration, IDs, and API impacts.
Data governance / security: Classify sensitive attributes and access rules.
Product/ops lead: Prioritize tradeoffs; approve changes.

Preparation checklist

Current ERD (conceptual) and a change log.
Glossary of entities/attributes (plain language).
A prioritized set of real questions and use cases; fictional or anonymized sample records for key entities.
CRUD matrix (which roles Create/Read/Update/Delete each entity).
Open issues and assumptions list.

How to run the review

Warm-up: Align on goals and scope (conceptual model only).
Walk the domain: For each entity, confirm its definition, business identifiers, required information, and allowed values. Record later implementation decisions separately.
Validate relationships: Read each relationship both directions; state cardinality and participation out loud (e.g., “ORDER must have exactly one CUSTOMER; a CUSTOMER can have zero or many ORDERS”).
Walk through information needs: Trace the entities, attributes, and relationships needed to answer each priority question; record missing information and ambiguous rules.
Decide and record: Log each change with rationale, owner, due date, and impact.
Close with acceptance criteria: Define what “ready for logical schema design and normalization” means and who signs off.

Elicitation techniques that work

Scenario walkthroughs: Start-to-finish journeys (create, amend, cancel, archive).
Counterexamples: “When does this rule not hold?” surfaces edge cases.
Attribute audit: Distinguish identifiers, natural keys, and optional attributes.
Lifecycle mapping: Draft states (e.g., Draft → Active → Closed) and state-dependent constraints.

Worked example: a customer with no orders

The draft ERD assumes every customer has at least one order. Ask a staff member who creates customer records to walk through a new customer registration.

From an interview answer to a revised relationship
Review step	Finding or decision
User answer	“We register customers before they buy anything. Some never place an order.”
Revised business rule	A customer may have zero or many orders. Every order still belongs to exactly one customer.
ERD change	Change the minimum at the ORDER end from one to zero. In Crow’s Foot notation, use a circle and crow’s foot beside ORDER and two bars beside CUSTOMER.
Logical schema implication	Allow a customer row with no matching order rows. Keep `Orders.CustomerID` as a `NOT NULL` foreign key: every order still requires a customer.
Validation scenario	Confirm that a newly registered customer can have no orders, while an order must identify an existing customer.

Guest checkout is a separate question. Having no login account does not necessarily mean having no customer record. Ask whether a guest order still identifies a customer, whether an account is a separate entity, and what information must be retained. Only an approved rule allowing an order without an associated customer would justify making this customer reference optional. A shopping cart also need not be modeled as a placed order.

Templates

Use the following quick templates during the session.

Interview agenda

Title: Final ERD Review (Conceptual)
Duration: 80 minutes (example agenda)
Participants: Domain Experts, Everyday Users, Analysts, Developers,
              Product Owner, Data Governance Representative

1) Context and objectives (5)
2) Entities and attributes review (20)
3) Relationships: cardinality and participation (20)
4) Top queries / reports validation (20)
5) Decisions and change log (10)
6) Sign-off criteria and next steps (5)

Decision / change-log entry

ID: CHG-007
Area: CUSTOMER–ORDER relationship
Change: Allow zero orders per customer; each order still requires one customer
Rationale: Customers can register before placing an order
Impact: Revise ORDER-end minimum to zero; retain the NOT NULL customer FK
Validation: Customer with no orders is valid; order without customer is invalid
Owner: [responsible person]
Due: [agreed date]
Status: [proposed / approved / implemented]
Approved by: [business owner and decision date]

Question bank (ask users)

Which reports do you run weekly, and what fields and filters do they require?
Can a customer exist before placing an order? Can an order exist without a customer? Ask both directions separately.
Which information is required when a record is created, and which becomes required later?
What makes two records duplicates, and who decides how to merge them?
Must reports preserve historical values, such as the delivery address used for a past order?
Which data is sensitive, and which roles may view or change it?

Readiness for logical design and normalization

Every entity has a clear purpose, agreed business identifiers, and the attributes needed by the approved use cases.
All relationships have explicit cardinality and participation on both ends.
Ambiguities and exceptions documented; none block core flows.
Priority information needs trace to defined attributes and relationship paths; missing facts and historical requirements are resolved.
Open items have owners and due dates; risks noted.

Common pitfalls

Conflating implementation choices (surrogate vs natural keys) with conceptual modeling.
Missing optionality rules (assuming “one” when “zero or one” is allowed).
Overwriting current values when users need history. Identify which states, events, or previous values must be retained.
Over-fitting to a single team’s vocabulary; use shared business terms.

After the interview

Update ERD and glossary; circulate the change log.
Resolve conflicts with the appropriate business owners; return decisions to affected users by an agreed date.
Record approval of the current model and its assumptions; proceed to logical schema design and normalization, revisiting the model when necessary.

Accommodating Client - Exercise

Before moving on to the next lesson, click the Exercise link one last time to try your hand at modifying the Stories on CD ER diagram to accommodate a revised business rule.
Accommodating Client - Exercise
The next lesson describes the process of “verbalizing” the ER diagram.