In most cases, data doesn’t exist on its own. Each record – whether its something measured in a research lab, collected from an online signup form, reported by an account executive, etc. – shares a relationship with some other record or type of data.

Think of a CRM that might consist of information about in-progress deals, contact lists, staffing lists, contract values, and more. Each type of record may be housed in its own table, but it doesn’t live in a table. There’s a direct link between the status of in-progress deals and the projected value of all signed and in-progress contracts, as there is a link between staffing lists, deal contacts, and deal statuses.

Relational databases, like their name implies, are built on these relationships between data records. Relationships take on different forms ranging in complexity and exclusivity, and understanding how to identify these relationships within your data and create them in a database is the first step towards creating powerful, effective, and well-organized relational databases.

Understanding relationships and references

Many records have relationships with other types of records. Some of these relationships are simple and exclusive, like a SIM card and a phone number. Some are more complex but still maintain some exclusivity, like a list of employees and a list of departments within a company, where each person is only assigned to one department, but each department consists of multiple employees.

Other relationships exist without exclusivity or restrictions, representing types of records that can be linked in any number of ways, like a list of Grist users and the workspaces they belong to, where a user can belong to any number of workspaces, and each workspace can be used by any number of people.

Relational databases use references to establish these relationships across tables and records, maintaining an organized structure and creating single sources of truth that help prevent human error when records are updated. References are also known as relations (hence the term relational database) – or foreign keys, referring to an identifier serving as the key of a corresponding record in a different “foreign” table.

In Grist documents, references are expressed via reference columns that create rich links between related records housed in different tables.

What’s the difference between a reference and a lookup?

If you’ve previously used lookup functions (LOOKUP, VLOOKUP, HLOOKUP, or XLOOKUP) in your spreadsheets, these types of relationships and references may be familiar. Lookup functions and reference columns both achieve the same goal – expressing relationships between records in different tables or sources of data – but reference columns don’t require any functions or formulas, meaning that they’re easier to set up and won’t give you any syntax-error-related headaches.

Reference columns in Grist are more feature-rich as well. Reference columns allow selecting values from a dropdown list as opposed to manually entering values, enable you to link to specific views, and let you view the entirety of the related record just by clicking on the related value.

By creating two-way references, reference columns also allow you to easily express the relationship between records both in the source table and in the related table. Learn more about the difference between one-way and two-way references below.

Using reference columns where you might otherwise use lookups is an important step in turning a collection of spreadsheets and data tables into a powerful database.

Types of relationships

The type of relationship considers the quantity of items that are being related, as well as whether or not there is exclusivity or uniqueness in the relationships themselves. A relationship between an individual and their government ID number – where one can be assigned to one – is very different from the relationship between a roster of students and a list of courses – where many can be assigned to many, with practically no limit on the possible combinations.

Understanding the nature of the relationships in your data is key to understanding the relationships in general, and will ultimately shape the way that references are created within your Grist documents.

There are four types of relationships that you might encounter in your data:

  1. One-to-One
  2. One-to-Many
  3. Many-to-One
  4. Many-to-Many

One-to-one relationships

A one-to-one relationship is an exclusive relationship between two unique records. When two sets of records have a one-to-one relationship, each individual record can only be related to one counterpart, and vice versa.

Some examples of one-to-one relationships include:

  • an individual and their birth certificate,
  • a company and its incorporating document,
  • an employee and their access badge, etc.

Consider the example above. Each record in the Name table is only related to one record in the ID # table. Once the relationship between two records has been created, neither record can be related to a different record for as long as that relationship exists.

In some cases, the specific data on either side of a one-to-one relationship may change – an employee may be issued a new access badge with a new unique identifier, for example – but the singular and unique nature of the relationship never does.

One-to-many relationships

A one-to-many relationship is a relationship in which one item or record can be related to any number of corresponding records.

One-to-many relationships are found in many common scenarios, including:

  • departments within a company and the employees that are assigned to them,
  • one person with multiple email addresses,
  • different countries in which a company has multiple regional offices, etc.

Although these relationships are less restrictive than one-to-one relationships, they still maintain an element of exclusivity. While the original record may be related to multiple other records, each of the records to which it relates can only be related to that record. For example, one employee may use a dozen company-issued devices, but each of those devices is only used by them.

Consider the example above. The Product department includes multiple employees – Bart and Steve – but each of those employees can only work for that department.

Many-to-one relationships

A many-to-one relationship is a relationship in which multiple items or records can be related to one corresponding record. Many-to-one relationships are the inverse of one-to-many relationships and share the same characteristics, including their partial exclusivity.

Many-to-one relationships include:

  • different factories producing products to be distributed by a single distributor,
  • many buildings under the control of a handful of property management companies,
  • a network of subsidiaries and shell corporations that are part of a large conglomerate, etc.

Like one-to-many relationships, many-to-one relationships also maintain an element of exclusivity. Note that any one-to-many relationship can be expressed as a many-to-one relationship (and vice versa) by changing which side of the relationship is considered to be the original record, and which is considered to be the related record.

Consider the example above, which is the inverse of the previous example. Bart and Steve both work in the Product department, but they cannot be assigned to any other department at the same time.

Many-to-many relationships

Many-to-many relationships are those in which any number of items or records can be related with any number of corresponding items or records. These types of relationships don’t require any exclusivity or uniqueness. One record may be related to ten others, and each of those ten may themselves be related to ten different records.

Some many-to-many relationships include:

  • a roster of students and the different courses they they take,
  • a team of developers, each working on multiple smaller teams,
  • the list of Grist users that are part of a team site and the various workspaces they use, etc.

Many-to-many relationships are the least restrictive of all four types, but they can express the most complex relationships.

One-way and two-way references

When you create a reference column in a Grist document, you are creating a relationship between two tables: the source table, in which the reference column is created, and the target table, in which the related data is housed.

By default, new reference columns are created as a one-way reference, meaning that the relationship is only shown in a column in the source table. Grist also allows you to configure a reference column as a two-way reference, meaning that the relationship will be shown as a reference column in the source table and as a reverse reference column in the target table.

One-way references

When you create a one-way reference, a reference column – populated with data from the target table – is established in the source table. No change is made to the target table itself.

With a one-way reference, the relationship between the two tables is only visible in the source table. By using more of Grist’s reference column features, you can populate the source table with additional data from the referenced rows in the target table.

If you’re using reference columns to create data overviews or summary tables, or to establish one table as a single source of truth for reference, a one-way reference may be most appropriate.

Two-way references

When you create a two-way reference, a reference column – populated with data from the target table – is established in the source table. Additionally, a reverse reference column – populated with data from the source table – is established in the target table.

With a two-way reference, the relationship between the two tables is visible regardless of which table you are looking at. In addition to accessing data from both the source and target tables, information can be changed on both sides of the relationship. For example, changing the department to which an employee is assigned can be done in either the reference column in the source table (Employee records), or in the reverse reference column in the target table (Department records).

Identifying and classifying relationships in your data

If you’re working with multiple data tables or spreadsheets, there are likely relationships that already exist within your data. Before you create these links in your Grist documents with reference columns, consider the nature of the relationship:

  1. What type of relationship exists between these records? Is there any kind of exclusivity or uniqueness to consider?
  2. How is this relationship best expressed in my document? Does it only need to be visible in the source table or should it be visible in the target table as well?
  3. Which data point links the two tables together and should act as the reference column? Is there a unique identifier? A name or category? Something that defines the relationship?

Learn more about creating reference columns in Grist in our Help Center.