Introduction
In my first week at the Data School, we talked about the topic of database structure and organization. As we were learning more, I thought this was such a foundational concept to understand. As a consultant, you don't always get a walkthrough of a client's data, and have to work through it yourself. In this post, I aim to breakdown the basics of database structure and explain why it's crucial to understand before your analysis.
What is a Relational Database?
A relational database is a collection of several data tables, all connected through relationships. Data is split up into multiple tables instead of one giant spreadsheet. Why can't we just have it all in one spreadsheet? To eliminate redundancy and save space!
For example, here's a table of orders. We have the Order ID, the customer name, and several other fields relating to a specific order. As you can see, John's name, email, and city shows on every single one of his orders. If John happens to move from New York to LA, you have to find and update every row that belongs to him. This takes time, and if you miss a row, now you have inconsistent data. Also, we have "Wireless mouse" and its price of $29.99 appearing on multiple rows. If the price changes, you again have to update every row where that product appears, and then create more risk for inconsistencies. Another thing--this table will get huge. Repeating the same customer and product information in thousands or millions of orders will waste storage and make updates all the more difficult.
Instead, you can split this into three separate tables:
Now, if John moves to LA, all we have to do is update one row in the Customers table. Same for the mouse price change in the Products table. The orders tables only stores what is unique to each order (order ID, who, what, how many, and when). Everything else is referenced by an ID. But how does the database know that C01 is John Smith or that P01 is a mouse? That's where keys come in.
Keys: What Hold a Database Together
A primary key (PK) is the column that uniquely identifies each row in a table. In the Customers table, that's Customer ID. In the Products table, it's Product ID. No two rows have the same primary key value and it belongs to one row and one row only. A foreign key (FK) is a column in one table that references the PK of another. This is what creates the relationship between tables! When you see Customer ID in the Orders table, it's there to point back to the Customers table and say look up "C01" there. That column in Orders is a FK, which references the PK of another table. So what does this new database structure look like?
Schemas
This image is called a relational schema. It's the structural blueprint of a relational database, which defines how data is organized into tables, the attributes (columns) each table contains, the data types, and their logical connections between them. Reading the schema, if available, is one of the first things you should do when starting a project. There are two very common types of schemas, shown below.
A star schema has a central fact table with measurable events (e.g. sales, shipments, etc.) surrounded by dimension tables (describes details about entities like customers or products). A snowflake schema takes that a step further where dimension tables can have subdimension tables. Like before, this is to remove redundancy and make things more storage efficient. This is definitely more complex, but cleaner in terms of data integrity.
In our Orders/Customers/Products example, our fact table is the Order table, since it holds measurable events (purchases, quantities, and dates). The dimension tables are the Customer and Product tables, which provide descriptive context for each order (who bought it, what they bought). So, our example is a simple star schema. The Order table sits in the center, which references the two dimension tables through their foreign keys (Customer ID and Product ID).
Why It Matters
When you arrive at a client, their database usually already exists and it's your job to understand it. Before starting analysis or building a dashboard, reading the schema will tell you what tables exist, where certain fields are, and how they're connected. Knowing the difference between a fact and dimension table tells you where to look for the numbers vs. any groupings or descriptions. Also, knowing the role of keys shows how to connect tables correctly. Skipping that step would lead to exploded data, duplicated rows, and inflated numbers. While database structure isn't always glamorous, it's the foundation everything is built on. Take the time to understand the schema, your analysis will be better because of it.