[Released] Data Modelling Guided Walkthrough

10 min of reading

We recommend keeping your audio on, as some content in this material is delivered through sound. If you prefer it muted, you can still follow along by reading the subtitles at the bottom of the screen.

Data Modelling Guided Walkthrough

Start

Learning Outcomes

By the end of this walkthrough, you’ll be able to…

Understand the purpose of a data model and how it provides a shared view of business information

Distinguish between conceptual, logical, and physical data models and know when each is used

Identify common elements in a data model

Distinguish between ERDs and class diagrams and when to use each

Key Takeaways

Purpose of data modelling → A data model defines the important entities, their attributes, and relationships, creating a shared vocabulary Role of data models → Provide both visual and textual representation of business information, supporting elicitation and requirements analysis, design, implementation, and improvement Business value → Makes information explicit and consistent, aligning both business and technical stakeholders

Entities/classes → Core objects of interest, each with a unique identifier Attributes → Properties of entities with values, formats, and constraints Relationships/associations → Define how entities are connected, with cardinality or multiplicity

Entity-relationship diagrams (ERDs) → Used in data models to show entities, attributes, and relationships Class diagrams → Used in class models to show classes, attributes, operations, and associations Object diagrams → Can be used to illustrate specific instances with sample attribute values

Purpose and Value of Data Modelling

01

Core Building Blocks of Data

Data Modelling Diagrams

03

04

Conceptual data model → Describes how the business understands and talks about its information, using shared business terms and relationships, without considering any technology or solution Logical data model → Takes the business concepts from the conceptual model and organizes them into a structured design with clear rules, so the data can be managed consistently in solution Physical data model → Shows how the data is implemented and stored in a database, including more information

Data Modelling Levels

02

*Explore data modelling levels and diagrams in more detail on the following pages.

The conceptual, logical, and physical data models represent three progressive levels of detail in data modelling. You start with the conceptual model, which defines high-level entities and relationships, capturing business needs without technical details. Next is the logical model, which adds structure by defining attributes and rules for organizing the data, focusing on logical arrangement. Finally, the physical model translates these into a database design, specifying tables, columns, data types, and constraints to show implementation in a specific system. Each model builds on the last, so even when modelling the same domain, their differences reflect distinct purposes and levels of abstraction.

Data Modelling Levels

Dive Deeper

Physical Data Model

Focus: Technical implementation—tables, indexes, storage, performance, and security details Purpose: Guide database developers to build the system in a specific technology Use case: To drive accurate database implementation and support performance tuning and scalability

Conceptual Data Model

Focus: High-level view of business information— main entities and relationships, but no technical details Purpose: Create a shared vocabulary across business and IT; capture how the business perceives its data Use cases: To align stakeholders on data needs

Logical Data Model

Focus: Structured, detailed design— normalized entities, attributes, and integrity rules Purpose: Prepare for solution design while staying technology-neutral Use cases: To guide database design in a technology-agnostic way; it's also useful for analytics planning

Core Building Blocks

An entity relationship diagram (ERD) is typically used to represent and visualize data models. This generic ERD highlights the core building blocks using the familiar context of online shopping. Click the flashing buttons to explore each building block in detail.

Entity Relationship Diagram

A key that uniquely distinguishes each instance of an entity. In a database, this unique identifier is implemented as a primary key (PK) in its own table. When other tables need to refer to that entity, they store the same value as a foreign key (FK) to create and enforce the relationship Examples: CustomerID, OrderID, ProductID, PaymentID

Unique Identifier

Entity

The main "things" the business cares about Examples: Customer, Order, Product, Payment

Relationships

How entities connect Examples: A Customer places Orders. An Order contains Products. An Order has one Payment

Cardinality defines how many instances of one entity can be associated with each instance of another. The notation used to represent this cardinality is called "crow’s foot" Examples: A Customer can place zero to many Orders An Order must have exactly one Customer An Order can include one to many Products

Cardinality

Properties or characteristics that describe each entity Examples: Customer → Name, Email, Address Order → Date, Status, Total Amount Product → Name, Price, Stock Qty Payment → Method, Transaction Date

Attributes

Examples of Data Models

Now that we understand the core building blocks of an ERD, let's look at some examples created by a subject matter expert from the community. We'll continue with the online shopping scenario to see how the three types of data models are represented in an ERD

A conceptual data model provides a high-level view of the business rather than technical details. It helps clarify who is involved in the business and how those parties are connected Entities in this model: Customer: Someone who places orders Order: A purchase made by a customer Product: The items a customer chooses to buy Relationships among entities (with cardinality): Customer <> Order

• Relationship: A Customer places an Order
• Cardinality:

a. A Customer can place one or many Orders b. An Order must be placed by exactly one Customer Order <> Product

• Relationship: An Order includes Products
• Cardinality:

a. An Order can include one or many Products b. A Product can be included in zero, one, or many Orders

Conceptual Data Model

Prefer listening over reading? Click here to play the audio, and click again to stop.

Created by Kamaljit Sagoo

Now that we understand the core building blocks of an ERD, let's look at some examples created by a subject matter expert from the community. We'll continue with the online shopping scenario to see how the three types of data models are represented in an ERD

Examples of Data Models

Logical Data Model

A logical data model builds on the conceptual model, keeping the core business ideas intact. Customers place Orders, and Orders include Products. What changes is:

• Entities now include attributes:
• Customer: Customer ID, First Name, Last Name
• Order: Order ID, Customer ID, Order Date
• Product: Product ID, Product Name

• Each entity can be uniquely identified:

• Customer: Customer ID
• Order: Order ID
• Product: Product ID

Overall, the model becomes more detailed and closer to implementation needs

Prefer listening over reading? Click here to play the audio, and click again to stop.

Created by Kamaljit Sagoo

Now that we understand the core building blocks of an ERD, let's look at some examples created by a subject matter expert from the community. We'll continue with the online shopping scenario to see how the three types of data models are represented in an ERD

Examples of Data Models

Physical Data Model

A physical data model shows how the database will be built. While the structure remains consistent with the logical model, here's what changes:

• Each entity is translated into a database table

• Every field has a specific data type (e.g. VARCHAR, DATE, DECIMAL)

• Primary keys are defined to uniquely identify each record

• Foreign keys are added to enforce relationships between tables

• Some relationships are implemented using linking tables (e.g., OrderItem)

• The database is configured with precise instructions on how data must be stored and connected

Prefer listening over reading? Click here to play the audio, and click again to stop.

Created by Kamaljit Sagoo

Test Your Knowledge!

Select the correct answer below

*Supports zoom-in for detailed viewing.

Data Diagrams created by Roberta Bortolotti

Diagram Comparison

So far, we’ve walked through the three levels of data models — conceptual, logical, and physical — and how they're typically represented using ERDs. Now, let’s shift gears and look at another type of diagram that may look familiar at first glance, but is used for a different purpose: a class diagram. You’ll often come across both ERDs and class diagrams in business analysis and system design. While they might appear similar, they focus on different questions and use different notations. Let’s place them side by side to see how they compare.

• Cardinality in an ERD defines how many data records may be related. It is commonly shown using crow’s foot notation.

• Multiplicity in a class diagram defines how many instances of a class can be associated with instances of another class. It is shown using numeric ranges.

Although they express similar ideas, the symbols and modeling intent are different.

• ERDs do NOT model behavior.

• Operations (methods) appear only in class diagrams and define what a class can do. For example, an Order class might have methods such as calculateTotal() or cancelOrder(), which describe actions the system can perform on an order.

Attributes appear in both diagrams.

• In an ERD, an attribute represents a piece of business data stored about an entity.

• In a class diagram, an attribute represents data defined on a class.

• A relationship in an ERD describes how entities are connected from a data perspective.

• An association in a class diagram describes how classes are connected within the system design.

• An entity in an ERD represents a business concept.

• A class in a class diagram represents a system object for that same concept.

What is it?

What does it answer?

What is it used for?

An ERD is used to model a system’s data and how it relates.

• What data do we store?
• How is the data related?
• What business rules apply to the data?

• Understanding business data requirements
• Designing or reviewing data models
• Supporting database design discussions

• What classes exist in the system?
• What responsibilities do they have?
• How does the system behave?

• Designing systems and software
• Communicating logic to developers
• Supporting implementation decisions

A class diagram is used to model a system’s classes and what they can do.

ERD

Class Diagram

Test Your Knowledge!

Complete this paragraph about data modelling with the correct concepts

Recap

Here’s what you've learned!

Data Modelling Levels

Conceptual → high-level, business view; shows main entities and relationships Logical → adds more detail with attributes, unique identifiers, and integrity rules, but stays technology-independent Physical → implementation view; shows tables, data types, keys, constraints, and performance/security details

Core Building Blocks

Entity/class: The key elements that matter (e.g., Customer, Order) Attribute: A property that describes those things (e.g., Customer Name, Order Date) Unique Identifier: A unique property that distinguishes each instance (e.g., CustomerID) Relationship/association: A connection that shows how entities or classes are linked Multiplicity/cardinality: Rules that define how many instances can be linked Operations: Actions or behaviours defined for a class

Data Modelling Diagrams

ERDs (entity relationship diagrams) are used to describe data

• They focus on entities, relationships, and how data is stored

• They're often used when designing or understanding databases

Class diagrams are used to describe system structure

• They focus on classes, attributes, and operations

• They're often used in application and software design

Purpose

Data modelling describes the things a business needs to know about and how those things are relatedIt builds a shared vocabulary so business and technical teams work with the same understanding of information

Contributors and Acknowledgements

We gratefully acknowledge the volunteers whose expertise and perspectives contributed to the development of this learning material.

Roberta Bortolotti

Kamaljit Sagoo

Congratulations, You’ve Levelled Up!

Start Over

Attributes can include such values as:

• Name: a unique name for the attribute. Other names used by stakeholders may be captured as aliases. For example, “Customer email” might also be called “User email” in some systems

• Values/Meanings: a list of acceptable values for the attribute. This can be expressed as: An enumerated list (e.g., Status = Open, Closed, Cancelled). An allowed format (e.g., Phone number = 10 digits; Date = YYYY-MM-DD). If abbreviations are used, the model should explain them (e.g., Order status = “O” (Open), “C” (Closed), “X” (Cancelled)).

• Description: a definition of what the attribute means in the context of the solution. This avoids ambiguity and ensures everyone understands it the same way. For example, Order date = the date when the customer placed the order, not the shipping date.

The Product table stores information about the products that the business sells. What changes is that: 1) PRODUCT_ID is defined as the primary key (PK) a. It uniquely identifies each product in the database b. Even if two products have similar names or descriptions, their PRODUCT_ID must be different c. Other tables (such as OrderItem) will reference products using this ID 2) Product attributes and data types for example, a. PRODUCT_NAME → VARCHAR(50) i. Stores the product name as text ii. Length is limited to keep storage efficient b. PRODUCT_DESCRIPTION → VARCHAR(200) i. Stores a longer text description of the product ii. Helps explain what the product is without affecting system logic c. PRODUCT_IN_STOCK → BOOLEAN i. Indicates whether the product is currently available ii. Uses true / false instead of text to avoid ambiguity Why this structure matters The database now knows: a. How to uniquely identify each product b. How to store product details consistently c. How to represent availability and pricing correctly This ensures: reliable product tracking, accurate pricing in orders, and clean integration with OrderItem and Order tables

The "things" can be physical (e.g., warehouse), organizational (e.g., sales area), abstract (e.g., product line), or event-based (e.g., appointment).

The OrderItem table connects Orders and Products in the database. What changes is that: 1) ORDER_ID and PRODUCT_ID together form a composite primary key a. Neither ORDER_ID nor PRODUCT_ID is unique on its own, together, they uniquely identify one line item in an order. This means: one specific product in one specific order 2) Both fields are also foreign keys a. ORDER_ID must match an ORDER_ID in the Order table, PRODUCT_ID must match PRODUCT_ID in the Product table. This enforces that: Every order item belongs to a real order; Every order item references a real product 3) QUANTITY is stored as an attribute a. It shows how many units of the product are included in the order. This information does not belong to Order or Product alone, it belongs to the relationship between Order and Product Why this structure matters 1) Orders and Products have a many-to-many relationship a. One order can include many products b. One product can appear in many orders 2) A relational database cannot store many-to-many relationships directly 3) OrderItem acts as a linking table to implement this relationship

The Customer table stores information about each customer in the database. What changes is that: 1) CUSTOMER_ID is defined as the primary key (PK) a. It uniquely identifies each customer record b. No two customers can have the same CUSTOMER_ID c. Other tables (such as Order) will use this value to reference a customer 2) Customer attributes and data types for example, a. FIRST_NAME → VARCHAR(50) i. Stores the customer’s first name as text ii. Length is limited to avoid unnecessary storage b. LAST_NAME → VARCHAR(50) i. Stores the customer’s last name ii. Treated as text, just like first name c. EMAIL → VARCHAR(60) i. Stores the customer’s email address ii. Used to contact or identify the customer Why this structure matters The database now knows: a. Which field uniquely identifies a customer b. How each piece of customer data must be stored This ensures: consistent customer records, accurate links between Customer and other tables, and reliable data usage across the system

The Order table stores information about each order placed by a customer. What changes is that: 1) ORDER_ID is defined as the primary key (PK) a. It uniquely identifies each order in the database b. No two orders can share the same ORDER_ID c. This allows the system to track each purchase separatelyr 2) CUSTOMER_ID is defined as a foreign key (FK) to link orders to customers a. It must match a CUSTOMER_ID in the Customer table b. This links each order to exactly one customer This allows the database to enforce a business rule: Every order must belong to a real, existing customer. 3) Order attributes and data types for example, a. ORDER_DATE → DATE i. Stores the date when the order was placed ii. Enables filtering and reporting by time Why this structure matters The database now knows: a. How to uniquely identify each order b. Which customer each order belongs to c. How to store dates and monetary values correctly This ensures: accurate order tracking, reliable customer–order relationships, and clean input for reporting and analytics