Understanding Keys and Relationships in Relational Schemas

Welcome to the next step in your data engineering journey! In our last lesson, we traced the path of data through a pipeline, from its source to its final destination. We saw that structured data often ends its journey in a storage system like a relational database. Now, we'll open the door to these databases and see how they're organized.

This lesson focuses on the blueprint of a relational database, known as a relational schema. You will learn to identify the three core components that give these databases their structure and power: primary keys, foreign keys, and the relationships they create between tables. Understanding this internal structure is the absolute foundation for working with structured data, as it's the key to retrieving, joining, and making sense of the information stored within.

The Problem with a Single Giant Spreadsheet

Before we dive into database theory, let's start with a practical problem. Imagine you run a small online store and you track all your orders in a single spreadsheet. For every order, you might record the order number, the customer's name, their address, and what they bought.

It works fine at first. But what happens when a customer, say "Jane Doe," makes a second order? You have to type her name and address all over again. If she makes ten orders, her information is duplicated ten times.

This creates several problems:

Data Redundancy: You're storing the same information repeatedly, which wastes space and is inefficient.
Data Inconsistency: What if Jane moves? You would have to find every single one of her past orders and update the address. If you miss one, your data becomes inconsistent. Which address is correct?
Update and Deletion Issues: If you delete Jane's only order, do you lose her customer information entirely? How do you add a new potential customer who hasn't placed an order yet?

This "flat file" approach clearly doesn't scale. The solution is the relational model, which breaks this one giant table into smaller, specialized, and connected tables.

The Relational Solution: Separate but Connected Tables

To see how this works in practice, let's watch a video by Eddie Woo. He does an excellent job of illustrating the problem of data redundancy in a flat file and then demonstrating how to solve it by creating a relational structure with keys.

Primary & Foreign Keys

This video provides a very clear, step-by-step explanation of why relational databases are necessary and how they work.

Please watch the following segments: First, watch the section from the beginning up to the 4:32 mark, where he explains the problem with data redundancy using a single table. Next, watch as he presents the solution: splitting the data into two tables, Orders and Customers, and introduces the idea of a customer_id. Finally, see how these tables are linked together to form the relationship, explaining the core concepts of primary and foreign keys.

The video gave you the core intuition. We solve the redundancy problem by creating two tables:

A Customers table, where each customer is listed only once.
An Orders table, which contains information specific to each order.

But how do we know which order belongs to which customer? This is where keys come into play.

Primary Keys: The Unique Identifier

As you saw in the video, each table in a relational database should have a primary key.

A primary key is a column (or a set of columns) that has a unique value for every single row in that table. Its job is to be the unambiguous identifier for a record.

In our example:

In the Customers table, the customer_id is the primary key. No two customers can have the same ID.
In the Orders table, the order_id is the primary key. Every order gets its own unique number.

A primary key has two strict rules:

It must be unique.
It cannot be empty or NULL.

For a more formal definition, let's turn to a short reading from Google Cloud's learning center.

What Is A Relational Database (RDBMS)?

This article provides a concise definition of the relational model, primary keys, and foreign keys using a clear Customer and Order table example.

Please read the section titled The relational model to solidify your understanding of primary and foreign keys. Then, continue reading through the example that shows how the tables are related.

Foreign Keys: The Bridge Between Tables

Now that we have unique identifiers for our customers and orders, we can build the bridge between them. This is the job of the foreign key.

A foreign key is a column in one table that refers to the primary key of another table.

In our example, the customer_id column in the Orders table is a foreign key. It doesn't identify the order (that's the job of order_id), but it tells us which customer in the Customers table this order belongs to. This link is what creates the relationship between the two tables.

This diagram illustrates a relational schema. The Customer ID in the Customers table is the Primary Key (PK). The Customer ID in the Sales table is a Foreign Key (FK) that points back to the primary key, creating a link between a sale and a customer.

This PK-FK link enforces a crucial rule called referential integrity. This means the database guarantees that a value entered into a foreign key column must already exist in the primary key column of the related table. In our example, you cannot create an order with a customer_id of 999 if there is no customer with id 999 in the Customers table. This prevents "orphan" records and keeps the data consistent.

To explore these rules and the concept of referential integrity more deeply, let's consult a more detailed resource from IBM.

What Is a Primary Key?

This article dives deeper into the characteristics of keys and the concept of referential integrity.

Focus on these three parts: Under the heading "What are the characteristics of a primary key?", read about the key constraints and the difference between natural and surrogate keys. A "surrogate key" like an auto-incrementing ID is what you'll see most often in data engineering. Next, jump to the "Foreign keys" subsection to read the formal definition and an example. Finally, read the section on "The significance of referential integrity constraints" to understand how the database enforces these links. Pay close attention to the example of the EMPLOYEE and DEPARTMENT tables and the accompanying diagram, which shows how an invalid record is prevented. The part explaining the parent-child relationship is particularly important.

Types of Relationships

The link created by a primary key and a foreign key defines a relationship. The most common type, which we've been using in our example, is a one-to-many relationship.

One customer can have many orders.
But each order belongs to only one customer.

This is represented in schema diagrams, often with symbols indicating the "one" side and the "many" side of the relationship. While there are other types (one-to-one and many-to-many), understanding the one-to-many relationship will cover the vast majority of scenarios you'll encounter early in your career.

To see how these relationships are visualized in a full database schema, let's watch one final video segment.

MySQL in 25 Minutes | Primary and Foreign Keys | Database Terminology for Beginners

This video from 365 Data Science provides a detailed walkthrough of creating a database schema and explains how to represent keys and relationships visually.

Please watch these two clips: First, watch the segment on primary keys, which explains their characteristics and how they are typically underlined in a schema diagram. Then, watch the following segment on foreign keys. It does an excellent job of showing how an arrow is used in a schema diagram to connect a foreign key to its corresponding primary key, visually representing the relationship. It also clearly contrasts the values you'd find in a PK column versus an FK column.

Conclusion

In this lesson, we've dissected the anatomy of a relational database. You've moved from understanding the high-level flow of data to seeing how it's meticulously organized for integrity and efficiency.

Here are the key takeaways:

Relational databases solve the problems of data redundancy and inconsistency by splitting data into multiple, related tables.
A primary key (PK) is a column that uniquely identifies every row in a table. It cannot be null.
A foreign key (FK) is a column in one table that refers to the primary key in another table, creating a link.
This PK-FK link establishes a relationship between tables, most commonly a one-to-many relationship.
Referential integrity is the database rule that ensures a foreign key value must match an existing primary key value, preventing orphan data.
A relational schema is the blueprint that shows all the tables, their columns, and the key-based relationships between them.

You now have the conceptual map of a relational database. In our next lesson, you'll put this knowledge into action. We will begin writing our first SQL queries to SELECT and filter data from these tables, which is the first step toward becoming a hands-on data practitioner.