Understanding Relationships

Until now, we have only queried a single table at a time. But the true power of a Relational Database lies in the relationships between multiple tables.

In a well-designed database, data is not duplicated. Instead of storing a user’s name and email on every single order they place, we store the user’s information once in a users table, and the order information in an orders table. We then link them together.

How do we create these links? Using Keys.

Primary Keys

A Primary Key (PK) is a column (or set of columns) that uniquely identifies every single row in a table. It is the absolute, unquestionable ID card for that piece of data.

In our users table, the id column is the Primary Key. No two users will ever have the same id. If Alice is user ID 1, she is the only user ID 1 forever.

Foreign Keys

A Foreign Key (FK) is a column in one table that references the Primary Key of another table.

In our orders table, we have a column called user_id. This is a Foreign Key. If an order has a user_id of 1, it means “This order belongs to the user whose Primary Key is 1 in the users table.”

Types of Relationships

By using Primary and Foreign Keys, databases establish different types of relationships:

One-to-Many: The most common relationship. One user can have many orders. One product can have many reviews.
Many-to-Many: Requires a “join table” in the middle. For example, an order can contain many products, and a product can appear in many orders. We solve this with an order_items table that sits between orders and products.
One-to-One: Less common. For example, a users table might have a one-to-one relationship with a user_security_settings table.

[!NOTE] This lesson is purely conceptual to help you understand why we join tables. In the next few lessons, you will learn the SQL syntax needed to actually stitch these tables together in a single query!

For now, try querying both the users and orders tables in the interactive lab to manually observe how the user_id connects the data.