Using relationships to connect data

In a relational database, relationships are used to connect different tables and establish links between different pieces of data. There are three types of relationships that can exist between tables: one-to-one, one-to-many, and many-to-many.

One-to-one relationships:

A one-to-one relationship occurs when one record in a table is related to only one record in another table. For example, you might have a table of employees and a table of login information, where each employee has a unique login. In this case, the employee and login tables would have a one-to-one relationship.

To create a one-to-one relationship between tables, you would typically use a foreign key. A foreign key is a field in one table that contains the primary key of another table. The primary key is a unique identifier for each record in a table. For example, you might have a primary key field called "ID" in your employee table and a foreign key field called "employee_id" in your login table. This would allow you to link each login to a specific employee.

Here's an example of a one-to-one relationship between the employee and login tables:

Employee table:

Login table:

One-to-many relationships:

A one-to-many relationship occurs when one record in a table is related to multiple records in another table. For example, you might have a table of customers and a table of orders, where each customer can place multiple orders. In this case, the customer and order tables would have a one-to-many relationship, with the customer table being the "one" side and the order table being the "many" side.

To create a one-to-many relationship between tables, you would use a foreign key in the "many" side table that references the primary key of the "one" side table. For example, you might have a primary key field called "ID" in your customer table and a foreign key field called "customer_id" in your order table. This would allow you to link each order to a specific customer.

Here's an example of a one-to-many relationship between the customer and order tables:

Customer table:

‍

Order table:

Many-to-many relationships:

Many-to-many relationships occur when multiple records in one table are related to multiple records in another table. For example, you might have a table of students and a table of courses, where each student can take multiple courses and each course can have multiple students. In this case, the student and course tables would have a many-to-many relationship.

‍

To create a many-to-many relationship between tables, you would typically use a linking table that has foreign keys that reference the primary keys of the other two tables. The linking table is used to establish the relationship between the other two tables, and it typically contains additional fields that provide more information about the relationship.

Here's an example of a many-to-many relationship between the student and course tables:

Student table:

Course table:

‍

Enrollment table:

To establish a many-to-many relationship between the student and course tables, we can add foreign keys to the enrollment table that reference the primary keys of the student and course tables:

‍

‍

‍