From user story to database: conceptual, logical and physical models and UML diagrams

We have all seen the project that starts straight with the code. It moves fast during the first week. Then a question comes up: "can a comment exist without an account?". Nobody had asked it, the database does not allow it and everything has to be reworked.

Design is there to ask these questions before they get expensive. It is not paperwork. It is the cheapest way to be wrong.

This article follows a single example from start to finish: a blog with visitors, members and an administrator. We start from the need, written as user stories, and end with the SQL script that creates the tables.

A chain, not a list of tools#

Each step answers a precise question and feeds the next one.

The first three steps come from UML and agile methods. The last three come from Merise, a French method that splits database design into these three levels. English-speaking teams usually talk about an ER diagram (entity-relationship) and a schema, but the three levels are the same everywhere. Mixing these tools is what companies do every day, and that is perfectly fine.

The user story#

The format#

A user story describes a need from the point of view of the person who benefits from it.

As a <role>,
I want <action>,
so that <benefit>.

Three examples for our blog.

As a visitor,
I want to search for an article by keyword,
so that I quickly find a topic that interests me.

As a member,
I want to edit my comment for a few minutes,
so that I can fix a typo.

As an administrator,
I want to be notified by email of a pending comment,
so that I can moderate it quickly.

The third part, "so that", is the one people forget most. Yet it is the most useful. It explains why the work is done, which makes it possible to suggest a better solution than the one requested.

Acceptance criteria#

A story without acceptance criteria can never be finished, since nobody knows what "done" looks like. The most common form comes from Gherkin: Given, When, Then.

Scenario: editing a comment in time
  Given a logged-in member who posted a comment 5 minutes ago
  When they edit the text and save
  Then the new text replaces the old one

Scenario: too late
  Given a logged-in member who posted a comment 20 minutes ago
  When they open the article
  Then the "Edit" button no longer appears on that comment

Each scenario becomes an automated test almost word for word. That is the real value of the format.

A good story follows INVEST#

• Independent: it can be delivered without waiting for another one
• Negotiable: it is the start of a discussion, not a frozen contract
• Valuable: it brings something to a user
• Estimable: the team can say roughly how much it costs
• Small: it fits in one iteration
• Testable: we know how to check that it is done

The traps#

The technical story in disguise.

As a developer,
I want to create the comments table,
so that comments can be stored.

No user benefits from a table. It is a task, and it belongs under a real story.

The story that is too big. "As a member, I want to manage my account" hides a sign-up, a login, a password change and a deletion. It is an epic. It has to be split.

The use case diagram#

What it shows#

It gives an overview: who uses the system, and to do what. It shows neither the screens, nor the order of actions, nor the data.

Its elements:

• the actor: a role outside the system, human or not. "Visitor", not "Paul"
• the use case: a goal of the actor, written with a verb
• the boundary: the limit of the system
• the relationships: association, generalisation, include and extend

The example#

Mermaid, which draws the other diagrams of this article, does not support use case diagrams. This one is therefore drawn with PlantUML, a tool that follows the UML notation: a stick figure for each actor, an ellipse for each use case and a hollow triangle arrow for inheritance.

Its source code, to paste as is on the PlantUML website or into an IDE plugin:

@startuml
left to right direction

actor Visitor
actor Member
actor Administrator

Member --|> Visitor
Administrator --|> Member

rectangle "Blog" {
  usecase "Read an article" as UC1
  usecase "Search for an article" as UC2
  usecase "Comment on an article" as UC3
  usecase "Reply to a comment" as UC4
  usecase "Check the content\n(anti-spam)" as UC5
  usecase "Moderate comments" as UC6
  usecase "Publish an article" as UC7
}

Visitor --> UC1
Visitor --> UC2
Visitor --> UC3
Administrator --> UC6
Administrator --> UC7

UC3 ..> UC5 : <<include>>
UC4 ..> UC3 : <<extend>>
@enduml

Relationships, without misreading#

Generalisation reads "is a". A member is a visitor. They can therefore do everything a visitor does, plus their own actions. No need to link the member to "Read an article": it is inherited.

Include marks a behaviour that the base use case always runs. Commenting always triggers the anti-spam check. The arrow goes from the base use case to the included one.

Extend marks an optional behaviour that only happens under a condition. Replying to a comment is a variant of "comment", used only when targeting a specific message. The arrow goes from the extension to the base use case.

The most common mistake is to split a use case into steps with include: "type the text", "click send", "show the message". That is no longer a use case diagram, it is a badly drawn flowchart. The flow of a use case is described elsewhere.

The written description#

The diagram is not enough. Each important use case comes with a description.

Use case: Comment on an article
Primary actor: Visitor
Precondition: the article is published

Main scenario
  1. The visitor enters their name, email and comment
  2. The system checks the content (anti-spam)
  3. The system stores the comment, pending moderation
  4. The system notifies the administrator by email
  5. The system shows a confirmation message

Alternative scenarios
  2a. The content is rejected: the system shows the form with the error
  3a. The actor is a logged-in member: the comment is published directly

Postcondition: the comment exists in the database

The sequence diagram#

What it shows#

It takes one scenario and shows who sends which message to whom, in which order. Time flows from top to bottom.

Its elements:

• the lifeline: a participant, with a vertical line below it
• the synchronous message (solid arrow, ->> in Mermaid): the sender waits for the answer
• the asynchronous message (open arrow, -)): the sender does not wait. A queued email, for example
• the return (dashed arrow, -->>): the answer to a message
• the activation bar: the period when the participant is working. In Mermaid, a + or - right after the arrow opens or closes it
• the fragments: alt for an alternative, opt for an option, loop for a loop

The example#

The scenario "a member comments on an article", as it really runs in a Laravel application.

sequenceDiagram
    actor Member
    participant Nav as Browser
    participant Router as Laravel router
    participant Req as StoreCommentRequest
    participant Ctrl as BlogController
    participant Model as Comment
    participant DB as Database
    participant Mail as Mail service

    Member->>Nav: submits the form
    Nav->>+Router: POST /blog/{slug}/comment
    Router->>+Req: validate the data

    alt invalid data
        Req-->>Nav: 302 back to the form + errors
    else valid data
        Req-->>-Router: validated data
        Router->>+Ctrl: addComment(request, slug)
        Ctrl->>Model: create(data)
        Model->>DB: INSERT INTO comments
        DB-->>Model: id
        Model-->>Ctrl: Comment

        opt reply to a comment
            Ctrl->>Mail: notify the targeted author
        end

        Ctrl-->>-Nav: 302 to the article + message
    end

    deactivate Router

One detail shows why the exercise is worth it. In Laravel, a FormRequest is validated before the controller is called. When the data is invalid, the controller never runs. Drawing the sequence forces you to know that.

The right level of detail#

A sequence diagram is not drawn code. You do not include getters or every function call. You keep the exchanges that matter to understand the scenario, and you draw one per important scenario, not one per method.

The three levels of data modelling#

The approach comes from Merise, born in France at the end of the 1970s. It splits database design into three levels.

• Conceptual: which data, without thinking about technology
• Logical: how to store it in tables
• Physical: how to write it for a specific DBMS

Each level is derived from the previous one with simple rules. That is what makes the approach so practical.

The data dictionary#

Before drawing, list the information to store, with its type and constraints.

It is also the moment to hunt down computed data. The number of comments of an article is not stored: it is counted.

The conceptual model#

The conceptual model contains three kinds of elements.

• The entity: an object of the domain, with its properties. USER, ARTICLE
• The identifier: the property that distinguishes each occurrence. article_id
• The relationship: a link between entities, named with a verb. WRITES, CONCERNS

Our conceptual model, in text form:

USER    (user_id, username, email, password)
ARTICLE (article_id, title, content, published_at)
COMMENT (comment_id, content, created_at)
TAG     (tag_id, label)

USER    0,n ---- WRITES ---- 0,1 COMMENT
ARTICLE 0,n ---- CONCERNS -- 1,1 COMMENT
ARTICLE 0,n ---- TAGGED ---- 0,n TAG
USER    0,n ---- LIKES ----- 0,n ARTICLE
                 (liked_at)

Reading cardinalities#

In Merise notation, cardinalities sit next to each entity. They say how many times one occurrence of this entity takes part in the relationship, first the minimum, then the maximum.

Always read from the entity towards the relationship:

• a user writes 0 to n comments
• a comment is written by 0 or 1 user

That 0,1 is not a mistake. On our blog, a visitor can comment without an account. The comment then has no user. It is exactly the question from the start of this article, and the conceptual model asked it before a single table existed.

On the other hand, a comment concerns exactly one article (1,1). A comment without an article makes no sense.

Be careful if you are used to UML class diagrams or to ER diagrams in crow's foot notation: there, multiplicities sit on the opposite side of the relationship. The meaning is the same, only the placement differs.

The relationship that carries data#

LIKES carries a property: the date when the member liked the article. This date belongs neither to the user nor to the article. It belongs to the pair. That is the sign it must live in the relationship.

The logical model: three mapping rules#

Rule 1. Each entity becomes a table. Its identifier becomes the primary key.

Rule 2. A relationship with an x,1 side and an x,n side disappears. The identifier of the entity on the x,n side moves as a foreign key into the table on the x,1 side.

• 1,1 side: the foreign key is required (NOT NULL)
• 0,1 side: the foreign key is optional (NULL allowed)

Rule 3. A relationship with x,n on both sides becomes a table. Its primary key is made of the two foreign keys. The properties of the relationship go there too.

The result, in text notation. The primary key is in bold, foreign keys start with #.

• USER (user_id, username, email, password)
• ARTICLE (article_id, title, content, published_at)
• TAG (tag_id, label)
• COMMENT (comment_id, content, created_at, #user_id, #article_id)
• TAGGED (#article_id, #tag_id)
• LIKES (#user_id, #article_id, liked_at)

WRITES and CONCERNS have disappeared: they became the foreign keys of COMMENT (rule 2). TAGGED and LIKES became tables (rule 3).

One last case is worth knowing: the 0,1 / 1,1 relationship, a one-to-one link. The foreign key goes on the 1,1 side, with a unique constraint to guarantee it only appears once.

Normal forms#

A well-built logical model follows at least the first three normal forms.

• 1NF: each column holds a single value. No tags column containing "php, laravel, sql"
• 2NF: 1NF, and each column depends on the whole primary key. In LIKES, a username column would only depend on user_id: it does not belong there
• 3NF: 2NF, and no column depends on another non-key column. Storing the postcode and the city in the same table copies the city every time

A correct conceptual model usually gives a logical model that is already normalised. Normal forms are mostly a way to check.

The physical model#

The physical model translates the logical model for a specific DBMS. It brings decisions that did not exist before: exact types, lengths, indexes, behaviour on deletion, naming conventions.

Here, MySQL with Laravel conventions: plural table names in snake_case. USER becomes users, TAGGED becomes article_tag. Renaming is part of the physical decisions, the model itself does not change.

CREATE TABLE users (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(30)  NOT NULL UNIQUE,
    email      VARCHAR(255) NOT NULL UNIQUE,
    password   VARCHAR(255) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE articles (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(255) NOT NULL,
    content      LONGTEXT     NOT NULL,
    published_at TIMESTAMP    NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE tags (
    id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(50) NOT NULL UNIQUE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE comments (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    content    TEXT            NOT NULL,
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id    BIGINT UNSIGNED NULL,
    article_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_comments_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_comments_article
        FOREIGN KEY (article_id) REFERENCES articles (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE article_tag (
    article_id BIGINT UNSIGNED NOT NULL,
    tag_id     BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles (id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id)     REFERENCES tags (id)     ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE likes (
    user_id    BIGINT UNSIGNED NOT NULL,
    article_id BIGINT UNSIGNED NOT NULL,
    liked_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, article_id),
    FOREIGN KEY (user_id)    REFERENCES users (id)    ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

The same schema, drawn by Mermaid:

erDiagram
    users |o--o{ comments : "writes"
    articles ||--o{ comments : "concerns"
    articles ||--o{ article_tag : "has"
    tags ||--o{ article_tag : "labels"
    users ||--o{ likes : "likes"
    articles ||--o{ likes : "is liked"

    users {
        bigint id PK
        varchar username UK
        varchar email UK
        varchar password
    }
    articles {
        bigint id PK
        varchar title
        longtext content
        timestamp published_at
    }
    comments {
        bigint id PK
        text content
        timestamp created_at
        bigint user_id FK "NULL allowed"
        bigint article_id FK
    }
    tags {
        bigint id PK
        varchar label UK
    }
    article_tag {
        bigint article_id PK, FK
        bigint tag_id PK, FK
    }
    likes {
        bigint user_id PK, FK
        bigint article_id PK, FK
        timestamp liked_at
    }

This diagram uses the so-called crow's foot notation. || means exactly one, |o zero or one, o{ zero or many. These are the cardinalities of the conceptual model, placed on the other side of the line: the |o next to users reads "a comment has zero or one author".

Each choice is justified by the conceptual model:

• user_id accepts NULL because the cardinality was 0,1
• ON DELETE SET NULL keeps the comments of a deleted account, without an author
• ON DELETE CASCADE on article_id deletes the comments with their article, since a comment cannot exist without one
• the composite primary key of likes prevents liking the same article twice
• utf8mb4 accepts every character, including the emojis visitors will not fail to write

With Laravel, the physical model is written as migrations rather than raw SQL. The reasoning stays exactly the same.

What about the class diagram?#

Outside France, data is usually modelled with a UML class diagram or an ER diagram. Both approaches look alike, but they do not serve the same purpose.

• the conceptual data model describes data and the links between them. Nothing else
• the class diagram describes objects: their data, but also their methods, inheritance and interfaces

With an ORM, the cardinalities show up directly in the code.

The last row matters. As soon as a relationship carries data, like the date of LIKES, JPA needs a real entity for the join table. A @ManyToMany cannot store an extra column.

What to keep in mind#

Design is a tool for discussion. A diagram nobody reads is useless. A diagram that makes someone say "oh no, a comment can exist without an account" has just saved a week.

You do not draw every diagram for every feature. A contact form does not need a sequence diagram. A feature that touches payments does.

A model lives with the project. When the database changes, the model changes too. Otherwise it lies, and a document that lies is worse than no document at all.

Further reading#

• Mermaid, the diagram syntax used in this article, which GitHub and GitLab also render directly
• Mermaid Live Editor, to try a diagram and see the result live
• PlantUML, for the full UML notation, including real use case diagrams
• The OMG UML specification, the official reference, to consult rather than read
• User Stories Applied by Mike Cohn, the reference book on user stories
• UML Distilled by Martin Fowler, short and pragmatic

One exercise to finish. Take a feature from your current project and write its user story with two acceptance criteria. Then draw the part of the data model it touches. If a cardinality makes you hesitate, that is a question to ask someone before coding.