Table Relations

Establishing relations between tables.

Foreign Key

A column in one table that is a primary key in another table. This creates a link between the two tables.

Visualizing a Foreign Key

  [users] table (Parent)       [posts] table (Child)
+----+----------+             +----+----------+---------+
| id | username |             | id | title    | user_id |  <-- Foreign Key
+----+----------+             +----+----------+---------+
| 1  | 'alice'  |-------|     | 1  | 'Post A' | 1       |
+----+----------+       |     +----+----------+---------+
                        |---> | 2  | 'Post B' | 1       |
                              +----+----------+---------+

The 'user_id' in 'posts' refers to the 'id' in 'users'.
            
One-to-Many

The most common relationship. Example: One `Author` can have many `Posts`. The `posts` table would have an `author_id` foreign key.

Visualizing One-to-Many

   [Author] table (One)      [Posts] table (Many)
+----+-------+             +----+--------------+-----------+
| id | name  |             | id | title        | author_id |
+----+-------+             +----+--------------+-----------+
| 1  | 'Jon' |----|        | 1  | 'Post One'   | 1         |
+----+-------+    |        +----+--------------+-----------+
                   |------->| 2  | 'Post Two'   | 1         |
                            +----+--------------+-----------+
                            | 3  | 'Post Three' | 2         |
                            +----+--------------+-----------+
            
Many-to-Many

Example: One `Post` can have many `Tags`, and one `Tag` can be on many `Posts`. This requires a third 'junction' or 'through' table (e.g., `post_tags`) that contains `post_id` and `tag_id`.

Visualizing Many-to-Many

  [posts] table        [post_tags] (Junction)       [tags] table
+----+----------+      +---------+--------+        +----+-------+
| id | title    |      | post_id | tag_id |        | id | name  |
+----+----------+      +---------+--------+        +----+-------+
| 1  | 'Post A' |----->|    1    |    1   |<-------| 1  | 'SQL' |
+----+----------+      +---------+--------+        +----+-------+
| 2  | 'Post B' |-\    |    1    |    2   |        | 2  | 'Py'  |
+----+----------+  \   +---------+--------+        +----+-------+
                    \->|    2    |    2   |------->| 3  | 'Web' |
                       +---------+--------+        +----+-------+