Three types of relationship exist between related tables. 

1️⃣ One-to-One

One record in a table is associated with one record, and only one record, in another table. 

For example, imagine a car rental company database. One table contains basic customer information (name, email etc), and another, related table contains each customers' active driving license. Each customer can have only one active driving license. And, similarly, each driving license can belong to just one customer.

2️⃣ One-to-Many

One record in a table is associated with zero, one, or multiple records in another table. 

For example, imagine the database for a zoo, with an Animals table and a Species table. Each species is associated with multiple animals, but each animal can only belong to a single species. Rufus and Jefferson, for instance, are both hippos and belong only to the Hippopotamus amphibius species 🦛🦛 (except Rufus, who's secretly half turtle 🐢).

3️⃣ Many-to-Many

Each record in both tables are associated with zero, one, or multiple records in the other table. 

For example, imagine a simple version of Slack's database, containing a Users table and a Channels table. Users are associated with all the channels they've joined and most channels contain multiple users.

4️⃣ A sneaky extra: Self-joining tables

In certain cases, the rows in a table may be associated with other rows in that same table. 

For example, imagine a company employee database containing an Employees table. Each row represents an employee and contains a unique ID. Some of the employees are also supervisors, which means they get first dibs on the good sandwiches at lunch 🥪🥪🥪. One of the columns in the table is called supervisor ID and contains the ID of the employee who acts as each employee's supervisor. 

So, if you wanted to look up a given employee's supervisor, you would just check the value in the supervisor ID column and then search for it in the ID column to locate that employee. 

And if you wanted to see a complete list of the employees overseen by each supervisor, you would join the table on itself by connecting the rows where the values in the Supervisor ID column match the values in the ID column. 

---------

Have a question?

We're on standby to help! To chat with us, click the blue Intercom button in the bottom right of Trevor's interface.