Modelling relationships

We previously described tables as spreadsheets with columns (=fields) and row. Where databases really distinguish themselves from spreadsheets is when you model relationships, which can be understood as a link from one table to another. What this means is that a field in one table "points to" rows in another table, because that field contains values of the row identifier in the related tables. The field that is pointing to another table is called the key (foreign key in the traditional database literature) and is available as an option in the Type drop-down when creating a new field.

Before you create fields that declare table relationships, it is important to consider what kind of relationship you have. This comes from considering the number of entities on each side of the relationship.

One-to-many relationships

The one to many relationship is the most common pattern in table relationships. The one to many relationship applies whenever you can apply this idiom: A has many Bs, and each B belongs to one A. For instance, a (larger) company consists of departments, and each employee belongs to one department. We model that with two tables, with a key on the employee table:

Department:
   Name: String

Employee:
   Name: String
   Salary: Integer
   Department: Key to Department

In all of these cases, the key goes on the table that belongs to one row of the other table. The key must go on this table, because if it went on the Department table it would have to contain an array of keys to employees, and in database design we try to avoid arrays as field types.

This database design holds even if we need to relax the assumption that a department has many employees and each employee belongs to a department. For example, there may be employees (such as the CEO) that do not belong to a department. This is accommodated by ensuring that the Department key in the employee table is not required. For the employees that do not have a department, that Key will then be missing (NULL in the traditional database literature).

The relationships may then be flipped if we assign a head to each department:

Department:
   Name: String
   Head: Key to Employee

Employee:
   Name: String
   Salary: Integer
   Department: Key to Department

There are now two one-to-many relationships. In addition to the previous relationship of each employee belonging to one department. The head relationship indicating that each department belongs to one employee (the head). This implies that one employee has many departments of which they are the heads. This is true, if we remember that many means 0 to infinity - most employees do not have a department of which they are the head. In addition, according to this design, one employee can be the head of multiple departments. If we wish to avoid that, we can impose a unique constraint on the Head field in the Department table.

A special case of the one-to-many relationship is when a table contains a key to itself. In the context of employees, we may do away with departments and instead decide that each employee reports to another employee (i.e. their superior or manager):

Employee:
   Name: String
   Salary: Integer
   ReportsTo: Key to Employee

Again, if ReportsTo is not required, there may be employees who do not report to any other employee, just as several employees can report to the same other employee, that is they have identical values for the ReportsTo field. A similar structure is used when modelling threaded comments on a blog where every comment except for the top-level comment has a parent comment.

One-to-one relationship

In a one-to-one relationship, there is one entity on either side of the relationship. One-to-one relationships are in fact rare, because it may be more convenient to put all the information in one table.

For instance, let's consider an electrician maintaining a database of their customers. The database designer decides to maintain separate tables for customers and their addresses. The two tables may look like this:

Customer:
   Name: String
   Telephone: PhoneNumber

Address:
   Street: String
   Town: String
   Postcode: String

The database designer must add a key to one of these two tables. As things stand, it doesn't matter which one. The database designer could add Address: Key to Address to the Customer table, or they could add Customer: Key to Customer to the Address table. This assumes that every customer has one address, and every address contains one customer.

How would we choose between the two? There could be reasons why the one-to-one assumption may be broken. For instance, if the electrician is serving the rich and famous, there may be more than one address for each customer, in which case this should be treated as a one to many relationship (one customer to many addresses). On the other hand, there may be more than one customer at every address, for instance if the electrician communicates with multiple residents in one household. In this case, the design could be treated as a one to many relationship, this time with one address too many customers.

There may be further considerations. For instance, the electrician may serve both residential and business customers. Instead of a single Customer table, they may choose to have a BusinessCustomer and a ResidentialCustomer table, in order to add business identification information to the business customers. However, address may remain as a single table. In this case, we may keep the one-to-one relationships. If we put the key in the Address table, we wouldn't know whether to make it a Key to BusinessCustomer or Key to ResidentialCustomer, but if we implement this by putting a Key to Address to each of the customer tables.

If none of these considerations apply, it may be worth considering containing all the information in a single table instead of breaking them into two tables. In this way, we would have a single Customer table containing the name, telephone number, address town and postcode as fields.

Many-to-many relationships

Sometimes, it is impossible to capture the relationship between two tables with one to many relationships. This is the case if there are many entities on both sides of the relationship. For instance, in the employee example, the company may have a table with projects and need to keep track of which employees work on which projects. If each employee can work on multiple projects and each project can have multiple employees working on it, a many to many relationship is required. This is done by introducing an intermediate table which contains keys to both tables. This intermediate table is sometimes called the join table.

Employee:
   Name: String
   Salary: Integer
   
Project:
   Name: String
   ContractValue: Integer

EmployeeProject:
   Employee: Key to Employee
   Project: Key to Project

The EmployeeProject is created in Saltcorn like any other table - there is nothing special about many to many relationships. This also means that you can add information to the join table, for instance you could add a field to EmployeeProject for the number of hours per week the employee is committed to this project, or the time they started or stopped working on the project.

There are however some special tricks for building applications with many to many relationships; see Interfaces for many-to-many relationships