Data Models

How to Build a Logical Data Model From User Stories

Turn user stories into entities, identifiers and relationships, then draw them as a crow's foot ERD, using a tool-lending library as the worked example.

Start From the Stories, Not the Screens

A logical model is built from what people say they need, not from what a screen happens to show. Here are four stories from a community tool library:

As a member, I want to reserve a tool for a date so that it is there when my project starts.
As a volunteer, I want to check out a tool to a member so that we know who has it.
As a volunteer, I want to record a return so that the tool can be lent again.
As a member, I want to renew a loan once so that I can finish the job.

Step 1: Collect the Nouns

Underline every noun that the business cares about: member, tool, reservation, date, volunteer, loan, return, renewal. That is your candidate list. It is always too long, and that is fine.

Step 2: Sort Each Candidate

Every candidate is one of five kinds of thing. Sorting them is most of the modeling work.

Candidate	Kind	Why
member, tool	Entity	You keep many of them, and each has its own identity
reservation, loan	Entity	Each has an identity and attributes of its own
return, renewal	Event	Something that happened to a loan, at a time, by someone
date	Attribute	It describes a reservation or a loan; it has no identity
volunteer	Role	A kind of user who records events, not data the library lends
tool category	Reference set	A short, fixed list of allowed values

Events and reference sets are the two kinds most often mis-modeled. We will spend a whole lesson on events.

Step 3: Give Each Entity an Identifier

A logical identifier is the value people say out loud: the member number on the card, the asset tag on the drill, the loan number on the receipt. Generated UUIDs are a storage decision, and they belong in the physical model.

If you cannot name an identifier, you may not have an entity. "Which one do you mean?" has to have an answer.

Step 4: Ask Two Questions About Every Relationship

For each pair of related entities, ask two questions in both directions:

How many? One, or many?
Must there be one? Is it mandatory or optional?

Say the answers as sentences: "One member can have zero or more loans. Every loan belongs to exactly one member." That sentence pair is the relationship. The crow's foot symbols are only shorthand for it.

Step 5: Resolve Many-to-Many Relationships

Members want many tools, and each tool is wanted by many members. A many-to-many relationship always hides a thing with attributes of its own. Here it is the reservation, which has a wanted-for date and a requested-at time. Promote it to an entity, and the many-to-many becomes two one-to-many relationships.

Step 6: Normalize to Third Normal Form

Every attribute must depend on the key, the whole key, and nothing but the key. Two violations appear in almost every first draft:

The member's email on the loan. It depends on the member, not the loan. Store it once, on the member.
The category's label on the tool. It belongs to the reference set. The tool holds only the category.

Each violation is a fact stored twice. Sooner or later the two copies disagree, and nothing tells you which one is right.

Step 7: Mark What Is Derived

Some attributes are answers, not facts: whether a loan is overdue, what its status is, whether a tool is available. Mark them as derived and do not store them. A stored answer goes stale the moment its inputs change.

Logical ERD of the tool library in crow's foot notation. MEMBER and TOOL each have zero or more RESERVATIONs and zero or more LOANs, and each LOAN has one or more LOAN EVENTs. Identifiers are bold, and derived values such as loan status and tool availability are greyed out. The takeaway: there are no foreign keys and no data types yet, because the logical model records what the data means, not how it is stored. — 2026-09-24 - provenance: rendered from the committed SVG in public/diagrams; alt re-read against the drawing on the same date

Mark What You Were Told Separately From What You Assumed

A first logical model always contains assumptions. "A loan can be renewed only once" might be policy, or it might be something you assumed. Label every attribute and relationship as one of two things:

STATED: said in a story or by the business owner.
ASSUMED: your guess, pending review.

The review meeting has one job: to turn every ASSUMED into STATED, or delete it. A model whose assumptions are invisible gets approved without anyone examining them.

Key Takeaways

Build from the nouns in your stories, then sort each one into entity, event, attribute, role or reference set.
A logical identifier is the value people say out loud. Surrogate keys wait for the physical model.
Every relationship is two sentences: how many, and whether there must be one.
A many-to-many relationship hides an entity. Promote it.
Normalize to 3NF, mark derived values, and label assumptions as ASSUMED until someone confirms them.

Example

markdown

%% Logical model in Mermaid. Mermaid requires a type slot on every attribute;
%% in a LOGICAL model fill it with a domain word, not a database type.
erDiagram
    MEMBER ||--o{ RESERVATION : places
    TOOL   ||--o{ RESERVATION : "is wanted in"
    MEMBER ||--o{ LOAN : borrows
    TOOL   ||--o{ LOAN : "is lent in"
    LOAN   ||--|{ LOAN_EVENT : records

    MEMBER {
        identifier member_number PK
        name       full_name
        email      email
        date       joined_on
    }
    TOOL {
        identifier asset_tag PK
        name       name
        refset     category "reference set"
    }
    RESERVATION {
        date      wanted_for "part of identifier"
        moment    requested_at
    }
    LOAN {
        identifier loan_number PK
        date       borrowed_on
        date       due_on
    }
    LOAN_EVENT {
        sequence  seq "with loan, the identifier"
        refset    kind "out, renewed, returned"
        moment    occurred_at
        person    recorded_by
    }

Example — MARKDOWN

%% Logical model in Mermaid. Mermaid requires a type slot on every attribute;
%% in a LOGICAL model fill it with a domain word, not a database type.
erDiagram
    MEMBER ||--o{ RESERVATION : places
    TOOL   ||--o{ RESERVATION : "is wanted in"
    MEMBER ||--o{ LOAN : borrows
    TOOL   ||--o{ LOAN : "is lent in"
    LOAN   ||--|{ LOAN_EVENT : records

    MEMBER {
        identifier member_number PK
        name       full_name
        email      email
        date       joined_on
    }
    TOOL {
        identifier asset_tag PK
        name       name
        refset     category "reference set"
    }
    RESERVATION {
        date      wanted_for "part of identifier"
        moment    requested_at
    }
    LOAN {
        identifier loan_number PK
        date       borrowed_on
        date       due_on
    }
    LOAN_EVENT {
        sequence  seq "with loan, the identifier"
        refset    kind "out, renewed, returned"
        moment    occurred_at
        person    recorded_by
    }

Where You'll See This in the Real World

Business analysts run exactly this noun sort in discovery workshops, often on a whiteboard with sticky notes. Its most valuable output is the list of ASSUMED items. Each one is a question to ask the owner before anyone writes a migration. Answering it in a meeting costs minutes; changing a table after it holds data costs a migration and a backfill.