Imagine you own a small online e-commerce company. You’ve got four separate data tables coming from different departments, each containing thousands of records, and you want to make sense of all this information to create a dashboard in tools like Tableau or Power BI. You need a way to link and organize these tables so they work together seamlessly. That’s where data modelling comes in.
Data modelling is like designing the blueprint for your data. Just as an architect plans out where everything goes in a house to make it functional and easy to navigate, data modelling helps structure your data so it’s logical, efficient, and easy to work with.
Let’s walk through how to build a good data model using the tables in the image above. Don’t worry if this is your first time hearing some of these terms – I’ll explain them as we go!
Step 1: Understanding Your Data
Before jumping in, take a close look at your data tables to get a clearer picture of what each table shows.
- Customers Table: Contains information about your customers – their unique ID, name, and email address.
- Orders Table: Tracks orders made by customers. Each order links to a customer and has details like the order date and the total amount.
- Products Table: Lists the products you sell, including their unique ID, name, and price.
- Order Details Table: Breaks down the relationship between orders and products, tracking quantities of each product in an order.
Step 2: Identifying Primary Keys and Foreign Keys
To organize and link tables effectively, the first step is to identify primary keys and foreign keys, which lay the foundation for defining relationships between tables:
- Primary Key (PK): A column (or combination of columns) that uniquely identifies each row in a table. For instance, “Customer ID” in the Customers Table is a primary key because each customer has a unique ID.
- Foreign Key (FK): A column in one table that refers to a primary key in another table. For example, “Customer ID” in the Orders Table is a foreign key because it connects each order to a specific customer.
Here's what this would look like:
- In the Customers Table, Customer ID is the primary key.
- In the Orders Table, Order ID is the primary key, and Customer ID is a foreign key referencing the Customer ID in the Customers Table.
- In the Products Table, Product ID is the primary key.
- In the Order Details Table, Order ID and Product ID together form a composite primary key. They are also foreign keys, linking to Order ID in the Orders Table and Product ID in the Products Table.
By identifying these keys, we establish the technical foundation for linking the tables and creating relationships, which we will explore further in the next step.
Step 3: Establishing Relationships and Cardinality
Now that we’ve identified the primary and foreign keys, we can define the relationships between the tables and their cardinality (the number of records in one table that relate to records in another). Defining these relationships allows us to link relevant data across all tables, making it easier to analyze and visualize. Here are common relationships and their cardinalities you should be aware of:
- One-to-One (1 to *): One record in Table A corresponds to exactly one record in Table B.
- One-to-Many (1 to *): One record in Table A corresponds to multiple records in Table B.
- Many-to-One (* to 1): Multiple records in Table A corresponds to exactly one record in Table B.
Here's what this would look like:
- Customers Table to Orders Table: A one-to-many relationship (1 to *), as one customer can place multiple orders.
- Orders Table to Order Details Table: A one-to-many relationship (1 to *), as one order can include multiple products.
- Products Table to Order Details Table: A many-to-one relationship (* to 1), as multiple entries in the Order Details Table can reference the same product.
Step 4: Normalizing Your Data
Normalization is a fancy word for organizing your data to avoid duplication and make it easier to work with. For example:
- Instead of storing a customer’s name and email in every row of the Orders Table, we only store their Customer ID and reference the Customers Table when needed.
- Instead of listing product details repeatedly in the Orders Table, we use an Order Details Table to manage which products belong to which orders.
The tables in this case are well normalized and ready to use.
Step 5: Designing Your Data Model
Now that we understand the relationships and cardinality, we can sketch and refine a proper data model. Here’s how we link the tables:
- Link Customers to Orders: Connect the Customer ID in the Customers Table to the Customer ID in the Orders Table (one-to-many).
- Link Orders to Order Details: Connect the Order ID in the Orders Table to the Order ID in the Order Details Table (one-to-many).
- Link Products to Order Details: Connect the Product ID in the Products Table to the Product ID in the Order Details Table (many-to-one).
This data model now ensures we can easily navigate between customers, their orders, and the products in each order.
Wrapping Up
Data modelling might sound intimidating at first, but it’s all about organizing your data so it’s easy to work with. By following these steps and creating clear relationships between your tables, you’ll have a solid foundation for building insightful reports and dashboards.
So, next time you’re handed a messy dataset, you’ll know exactly how to turn it into a clean and powerful data model. Happy modelling!