Relationships in databases

Relationships in databases

In databases, relationships define how tables are linked to each other. The most common types of relationships are:

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

Each row in Table A is linked to one and only one row in Table B, and vice versa.

Example: A person and their passport. Each person has one passport, and each passport is assigned to one person.

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

A single row in Table A can be associated with multiple rows in Table B, but each row in Table B is linked to only one row in Table A.

Example: A customer and their orders. A customer can place multiple orders, but each order is associated with only one customer.

3. Many-to-One (N:1):

This is essentially the reverse of a One-to-Many relationship. Multiple rows in Table A are linked to one row in Table B.

Example: Many employees working in one department.

4. Many-to-Many (M:N):

Multiple rows in Table A can be related to multiple rows in Table B. Typically, this is implemented using a junction (or bridge) table to break the relationship into two One-to-Many relationships.

Example: Students and courses. A student can enroll in many courses, and a course can have many students.

5. Self-referencing relationship:

A table can have a relationship with itself. A row can be related to another row within the same table.

Example: Employees and managers. An employee can have a manager, who is also an employee in the same table.

Each type of relationship is defined using primary keys and foreign keys to maintain data integrity.