Introduction

When working with data, the data is usually stored in different tables. A good example, a car sales business could have one table containing car information, another containing car information and another containing sales transaction.
Therefore, data modelling, relationships, and joins help us join the data together to make it easy to understand and analyze it.
A good data modelling will ensure that information is well organized correctly, reports and calculations are done for accurate results.

Learning Objectives

By the end of this article, you should be able to understand the following:

. Understand what data modelling means.
. Explain why data modelling is important.
. Understand relationship between tables.
. Explain the different types of relationships.
. Understand what joins are.
. Identify common types of joins.
. Understand how relationships and joins are used in data analysis.

### 1. What is Data Modelling?
Data modelling is the process of organizing data into tables define how they are connected.
Instead of putting all the information into one large table, we separate the data into different tables based on what it represents.

For example, a car business may have:

Customer Table

Customer Table Customer Name Region
C001 David Nairobi
C002 Clara Mombasa

Car Table

Car ID Car Model Brand
CAR01 Fortuner Toyota
CAR02 CX5 Mazda

Sales Table

Order ID Customer ID Car ID Sales Amount
0001 C001 CARO1 8,500,000
0002 C002 CAR02 4,500,000

These tables are connected using fields like the Customer ID and Car ID.

2. Why is Data Modelling Important?

A well-designed data model is important because it helps us:

Organize data
It keeps related information in appropriate tables instead of putting everything into one large table.

Reduce Duplication
The same customer does not need to be repeated in every sales record.

Improve reporting
A good model makes it easier to create accurate reports and dashboards.

Support calculations
In tools like Power BI, a good data model helps DAX calculations work correctly.

Improve Performance
Well-organized tables and relationships will help one to report process data more efficiently.

Make the model easier to maintain
When data is well organized properly, it is easier to update, clean and manage.

3. What is a Relationship?

A relationship is a connection between two tables.
For example, the Customer ID in the Customer table can be connected to the Customer in the Sales Table. The Customer table may contain one record for each customer, while the Sales table can contain many sales made by the same customer.

This creates a one-to-many relationship.

For example:

Customer

C001 → David

Sales

C001 → Order 001
C001 → Order 005
C001 → Order 010

One customer can therefore have many sales records.

4. Types of Relationships

There are several types of common types of relationships.

One-to-One (1:1)
One record in one table matches one record in another table.

Example:
One person has one passport record.

One-to-Many (1:M)
One record in one table can match many records in another table.

Example:
One customer can make many orders.
This is one of the most common relationships in data modelling.

Many-to-Many (M:M)
Many records in one table can be related to many records in another table.
For example, one student can take many courses, and one course can have many students.
Many-to-many relationships need to be handled carefully because they can sometimes cause incorrect calculations or duplicate results.

5. Primary Key and Foreign Key

Keys are important when creating relationships.

Primary Key
A primary key is a column that uniquely identifies each record in a table.

For example:

Customer ID Customer Name
C001 David
C002 Clara
C003 Kevin

Here, Customer ID can be the primary key because every customer has a unique ID.

Foreign Key

A foreign key is a column that refers to the primary key of another table.
For example, the Sales table may contain Customer ID:

Order ID Customer ID Sales Amount
O001 C001 8,500,000
O002 C002 4,500,000

Here, Customer ID is a foreign key because it connects the Sales table to the Customer table.

6. What is a Join?

A join is a way of combining information from two or more tables using a common column.
For example, suppose we have a Customer table and a Sales table.
The Customer table tells us who the customer is, while the Sales table tells us what they purchased.

A join can combine these pieces of information.
For example:

Customer ID Customer Name Order ID Sales Amount
C001 David 0001 8,500,000
C002 Clara 0002 4,500,000

The tables have been joined using Customer ID.

7. Common Types of Joins

Inner Join
An inner join returns only records that have matching values in both tables.
For example, if a customer exists in both tables, that customer's information will be included.

Left Join
A left join returns all records from the left table and matching records from the right table. If there is no match, the information from the right table will normally appear as null.

Right Join
A right join returns all records from the right table and matching records from the left table.

Full Outer Join
A full outer join returns all records from both tables.
Records are included whether or not they have a matching value in the other table.

8. Relationships vs Joins

Relationships and joins are related, but they are not exactly the same.
A relationship defines how tables are connected in a data model.
A join combines data from tables based on a matching column.

For example:
In Power BI, relationships are commonly used to connect tables in a data model.
In SQL, joins are commonly used to combine data from different tables.
In Power Query, merge operations can be used to join tables.

9. Example Using a Car Dataset

Imagine you have a car sales dataset with three tables:

Customers

. Customer ID
. Customer Name
. Customer Age
. Region

Cars

. Car ID
. Car Model
. Brand
. Selling Price

Orders

. Order ID
. Customer ID
. Car ID
. Order Date
. Units Sold
The tables can be connected like this:

Customers → Orders ← Cars

The Customer ID connects Customers to Orders, while Car ID connects Cars to Orders.

This model allows you to answer questions such as:
a). How many cars did each customer buy?
b). Which car brand generated the most sales?
c). Which region had the highest sales?
d). How much revenue was generated each month?

#### 10. Data Modelling in Power BI
In Power BI, data modelling is especially important when creating reports.
You can load different tables into Power BI and create relationships between them. A common structure is a star schema.

A star schema normally has:
. Fact table – contains transactions or measurements, such as sales.
. Dimension tables – contain descriptive information, such as customers, cars, products, or dates.

For example:
Customer Dimension → Sales Fact ← Car Dimension

This structure makes the data easier to analyze and can support efficient DAX calculations.

Conclusion

Data modelling is an important part of data analysis. It helps organize information into tables and shows how those tables are connected.
Relationships connect tables, while joins are used to combine data from different tables.
Understanding primary keys, foreign keys, relationship types, and joins is important for anyone learning SQL, Power BI, Power Query, or data analytics.

A well-designed data model makes reporting, analysis, calculations, and data management easier and more reliable.