# 3.2.1: SQL 1-M Relationships

## Learning Objectives

1. SQL relationships associate data between 1 or more SQL tables to model logical relationships and make data querying more modular and efficient
2. There are 3 kinds of relationships in SQL: one-to-one (1-1), one-to-many (1-M) and many-to-many (M-M)
3. Understand how SQL represents 1-M relationships in its databases

## Introduction

Imagine a social media app with `Users` and `Posts` tables in its SQL database. How would the app know which posts belong to which users? The answer is SQL relationships.

To model the fact that each user can have 0 or more posts, but each post can belong to at most 1 user, we can implement a so-called one-to-many relationship (aka association) between users and posts.

SQL implements relationships using the concept of "primary" and "foreign" keys. Every table has a "primary key" (typically the `id` column) that uniquely identifies each row (aka record, entry) in the table. Tables can have columns that reference primary keys of related tables to define relationships between those tables. Primary keys referenced in other tables are known as "foreign keys", because from the perspective of the tables referencing the primary keys, those keys belongs to "foreign" tables.

The following is a sample `Users` table where `id` is the primary key. There are no foreign keys on this table.

| id (primary key) | username |
| ---------------- | -------- |
| 1                | foong    |
| 2                | kai      |

The following is a sample `Posts` table where `id` is also the primary key, but each post also belongs to a `User`, and specifies a `userId` to identify the user the post belongs to. `userId` is known as a "foreign key", because it references the "primary key" of another table.

| id (primary key) | text                      | userId (foreign key) |
| ---------------- | ------------------------- | -------------------- |
| 1                | "Go Rocket!!!!!!!!!!"     | 1                    |
| 2                | "Rocket is the Best!!!!!" | 1                    |
| 3                | "🚀🚀🚀🚀🚀🚀🚀🚀🚀🚀"    | 2                    |

The foreign key always lives in the "many" table in the one-to-many relationship. In our above example, notice the foreign key lives in the `Posts` table and not the `Users` table, and it cannot be the other way around. If we tried to put a `postId` or `postIds` foreign key in the `Users` table, we would find that we would need to either duplicate `User` rows for each user's posts, or store an array of post IDs in the foreign key column. The former would repeat `User` data unnecessarily, and the latter would make SQL harder to query because of the array structure within a single cell of the table.

The above architecture of SQL relationships makes SQL efficient at storing data. The alternative might be to have a single `Posts` table where we store each `User`'s data repeatedly in every post, but that would be wasteful and inefficient, especially when our `Users` table contains more data such as contact information and preferences.

SQL stores one-to-one relationship data in the same way as one-to-many, except that the foreign key can live in either table. One-to-one relationships are less common because most of the time all the data in one-to-one relationships can be stored in a single table. Like one-to-many relationships, in one-to-one relationships there should only be 1 table with the foreign key. Examples of one-to-one relationships include users and their relevant contact and auth information if we were to separate that information into multiple tables for more efficient querying. 

## Querying related data using SQL

Given the above `Users` and `Posts` tables, we can query for related data using primary and foreign key relationships.

For example, to query all posts that belong to `kai`, I could run 2 queries. The 1st query retrieves the `id` of the user with username `kai`.

```sql
SELECT id from Users where username='kai';
```

The above query would return `2`, `kai`'s user ID. The 2nd query retrieves the posts that belong to the user with that user ID, and should return all of `kai`'s posts.

```sql
SELECT * from Posts where userId=2;
```

We will not perform such queries with raw SQL in our apps, but we may need to perform such queries when performing analytics on our companies' databases outside of an app context. In our apps we typically use ORMs like Sequelize that make querying for related data more robust, with commands such as `user.getPosts()`. More on querying data with Sequelize in Sequelize submodules.

## Additional Resources

1. [Database normalisation theory](https://en.wikipedia.org/wiki/Database_normalization) suggests ways to structure our SQL database using an optimal number of 1-M and M-M relationships to minimise redundancy. This is especially important for companies processing large volumes of data.
2. If you want to define relationships with pure SQL you should read this [Stack-overflow article](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de).

{% embed url="<https://www.youtube.com/watch?v=zsjvFFKOm3c>" %}
SQL in 100 seconds
{% endembed %}