Skip to main content

Three Types of Relationships Between Worksheets

This article outlines the three types of relationships between worksheets and provides examples to help you better understand the usage of the Relationship control.

An application can be viewed as a database, containing multiple worksheets. The records in different worksheets may have the following three types of relationships:

  • One-to-One Relationship
  • One-to-Many Relationship
  • Many-to-Many Relationship

One-to-One Relationship​

This is a simple type of relationship. For example, a product can only have one product ID, and an employee can only have one employee ID. Conversely, an employee or product can only be found using their respective ID.

This represents two attributes (e.g., employee name and employee ID, product name and product ID) of a single object (e.g., employee, product).

Typically, we only need to store these one-to-one attributes in a single worksheet, without the need to create separate worksheets for management.

Another type of one-to-one relationship exists between different objects within the same worksheet. For example, in a table tennis doubles match, each player has a one-to-one relationship with their partner.

One-to-one relationships are relatively rare. Below are some additional examples of one-to-one relationships:

  • Country and Flag: Each country has only one national flag, and each flag belongs to only one country.

  • Spousal Relationship: Each person has only one spouse.

One-to-Many Relationship​

The one-to-many relationship is more complex but also more common. For example, a customer can have multiple orders, but each order belongs to only one customer. The customer worksheet stores basic customer information (e.g., contact person, company address), while the order worksheet stores basic order information (e.g., customer, order date, delivery date, order amount). The relationship control is used to establish a connection between the two worksheets (since both require customer information).

As shown below, one customer is related to multiple orders.

Here is the detail page of a customer record.

An order can only belong to one customer.

Below are some additional examples of one-to-many relationships:

  • Project-Task: Each task belongs to only one project, but a project can contain multiple tasks.

  • Manager-Subordinate: Each employee has only one direct manager, but a manager can have multiple subordinates.

Many-to-Many Relationship​

A common example of a many-to-many relationship is the relationship between members and tasks. A member can participate in multiple tasks, and a task can have multiple participants. The member worksheet and task worksheet are related by the relationship control. When viewing a member, you can see all the tasks they are involved in. Clicking on any record will directly open the task and display its participants.

Below are some additional examples of many-to-many relationships:

  • Doctor-Patient: Each doctor sees many patients, and each patient sees many doctors.

  • Customer-Product: One customer can purchase many products, and one product can be purchased by different customers.