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' |
+---------+--------+ +----+-------+