Data Relationships

When looking at splitting large tables into smaller ones and creating relationships between those new tables it is important to understand the different data relationships available.

• One-to-One (1:1): A row in Table A corresponds to only one row in Table B. Often used for security or splitting large tables.
  • Example: A Employees table and a MobilePhones table.
• One-to-Many (1:N): A row in Table A can relate to many rows in Table B, but a row in Table B relates to only one in Table A. This is the most common type.
  • Example: A Customers table (one) and an Orders table (many).
• Many-to-Many (M:N): Multiple rows in Table A relate to multiple rows in Table B. This requires a junction/linking table to implement.
  • Example: A Students table and a Courses table.

One-to-One (1:1)

A one to one relationship refers to 1 row in Table A being linked to 1 (and only 1) row, or no rows in Table B and vice versa. Imagine a company with a table of employees and a table of company mobile phones. Each employee is only assigned 1 mobile phone. Each mobile phone can only be assigned to 1 employee. However, not all employees are given a company mobile phone.

One-to-Many (1:N)

A one to many relationship refers to 1 row in Table A being linked to many rows in Table B and many rows in Table B linked to 1 row in table A. Imagine a company with a table of customers and a table of sales invoices. Each customer can have many sales invoices but each sales invoice can only be for one customer.

Many-to-Many (M:N)

A many to many relationship refers to many rows in Table A being linked to many rows in Table B and vice versa. Imagine a college with a table of students and a table of classes. Each class will have many students taking it and each student will take many classes. In order to implement a many to many relationship in a database a Bridge table sometimes referred to as a Linking table is used to split the many to many relationship into two one to many relationships.

Self Referencing

A self referencing relationship refers to when a table relates to itself. Imagine an employee table with a column for manager ID that column refers back to the employee ID of the manager.