This article is a continuation of Part 1. Make sure you have read it before jumping in.

7. Relationships in tables

Relationships define how two or more tables are connected using foreign keys.

Types of relationships -

1. One - to - One (1:1)

One record in Table A is related to one and only one record in Table B. Its vice-versa also true.

Example - Each Creature has exactly one DNA profile, and each DNA profile belongs to exactly one Creature.

ALTER TABLE dna_profiles
ADD CONSTRAINT fk_creature FOREIGN KEY (creature_id) REFERENCES creatures(id),
ADD CONSTRAINT uq_creature UNIQUE (creature_id); -- the creature_id must be unique

2. One - to - Many (1:M)

One record in Table A can be related to many records in Table B, but each record in Table B is related to only one record in Table A.

Example - One Customer can purchase many Creatures, but each purchase belongs to only one Customer.

ALTER TABLE purchases
ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

3. Many - to - Many (M:M)

One record in Table A can relate to many records in Table B, and its vice-versa also true. We need a third table for implementing this kind of relationships

Example - Creatures can have multiple caretakers, and caretakers can be assigned to multiple creatures.

CREATE TABLE creature_caretakers (
creature_id INT,
caretaker_id INT,
PRIMARY KEY (creature_id, caretaker_id),
FOREIGN KEY (creature_id) REFERENCES creatures(id),
FOREIGN KEY (caretaker_id) REFERENCES caretakers(id)
);

Advanced Relationship

1. Junction Tables

   When there is many - to - many relationship between two tables, we can not directly connect them. We need a seperate table to store references for those two tables, called junction table.

   In the many to many example above, where we discussed that creatures can have multiple caretakers and caretakers can be assigned to multiple creatures, we create a junction table called creature_caretakers, where we store creature_id and caretaker_id.

2. Self-Referencing Relationship or Recursive Relationship

   When a table needs to relate to itself, it is called a self-referencing relationship. For self referencing we use a foreign key that points back to the same table.

   The most common example of a self-referencing relationship is a file and folder structure, where each folder can contain subfolders, and each subfolder can itself have more subfolders. It creates a parent and child relationship within the same table.