🔗 Customers, Orders, Products, Reviews — How a BA Maps the Invisible Relationships Between Everything

💬 Last week we followed data as it flowed through the order management system.

We saw where it came from, where it went and where it lived.

This week we zoom into the data itself.

Not how it flows — but how it connects.

A customer places many orders. An order contains many products. A product has many reviews.

Who maps all of this?

The BA. With an Entity Relationship Diagram. 🔗

🎬 Scene: E-Commerce HQ, Database Design Meeting

The e-commerce platform is building a new personalisation engine.

The tech lead turns to the BA.

“Before we design the database — can you map out all the data entities and how they relate to each other?”

The BA opens a blank diagram.

She starts with three words: Customer. Order. Product.

By the end of the meeting there are nine entities and twenty-three relationships on the board.

The tech lead takes a photo. “This is exactly what we needed.” 🔗

🤔 What Is an Entity Relationship Diagram?

An Entity Relationship Diagram — or ERD — shows the data entities in a system and the relationships between them.

It answers:

❓ What types of data does the system store?

❓ How does each piece of data relate to other pieces?

❓ How many of one thing can connect to how many of another?

It is the data blueprint before the database is built. 🗺️

✅ Step 1: Identify the Entities

An entity is any thing the system needs to store information about.

For the e-commerce personalisation engine:

📦 Customer — name, email, address, preferences

📦 Order — order ID, date, status, total value

📦 Product — name, description, price, category, stock level

📦 Review — rating, comment, date, verified purchase flag

📦 Category — name, parent category, display order

📦 Wishlist — customer ID, product ID, date added

📦 Delivery — order ID, address, carrier, tracking number, status

Each entity becomes a table in the database. Each attribute becomes a column. 🎯

✅ Step 2: Define the Relationships

Relationships describe how entities connect to each other.

For the e-commerce platform:

🔗 A Customer PLACES many Orders — but each Order belongs to one Customer

🔗 An Order CONTAINS many Products — and a Product can appear in many Orders

🔗 A Customer WRITES many Reviews — but each Review is written by one Customer

🔗 A Product BELONGS TO one Category — but a Category contains many Products

These relationships control how the database is structured and how data can be queried. 🗃️

✅ Step 3: Add Cardinality — The Numbers Game

Cardinality describes HOW MANY of one entity can relate to how many of another.

There are three types:

1️⃣ One-to-One — one Customer has one Account Profile

1️⃣➡️♾️ One-to-Many — one Customer places many Orders

♾️➡️♾️ Many-to-Many — many Products appear in many Orders

Many-to-Many relationships need a joining table in the database.

For Orders and Products: the joining table is called Order Line Items — it stores which products, in what quantities, at what price, for each order.

📌 BA Tip: Every Many-to-Many relationship in an ERD creates a new data entity the database team needs to build. Always flag these early — they add complexity and time to development. 🔍

✅ Step 4: Validate With Both Business and Technical Stakeholders

An ERD needs sign-off from two very different groups:

💼 Business stakeholders — does this reflect how the business actually works?

💻 Technical stakeholders — is this buildable and scalable?

For the personalisation engine ERD the marketing team adds one correction:

“A Customer can have multiple Wishlists — not just one. We want seasonal wishlists.”

One relationship updated. One database table restructured. Before a single line of code was written. ✅

🎯 The BA Takeaway

An ERD is the bridge between business requirements and database design.

✅ Identify all entities — everything the system needs to store data about

✅ Define relationships with clear verbs — places, contains, writes, belongs to

✅ Add cardinality — how many of each entity can relate to how many of another

✅ Flag Many-to-Many relationships — they always create extra complexity

✅ Validate with both business and technical stakeholders

🎬 Closing Scene

The database design meeting ends.

The tech lead has the ERD saved. The developer has already started sketching the table structure.

The marketing manager looks at the diagram one more time.

“I never thought about how all of this connects. It’s actually quite beautiful.”

The BA saves the file.

“Data always has a structure. Someone just needs to draw it.” 🔗💼