About table-to-table relationships

In Quick Base, a table-to-table relationship helps you organize the data in your app. If you have data that is common across what you need to track, a relationship lets you enter the data once and make references to it later, rather than entering it over and over.

A relationship is an association, or connection, between two tables. When you relate two tables, you save time because you don't need to enter the same info in separate tables. Projects have multiple tasks, a company could have multiple locations, or several books can be written by the same author. In a relationship, you save time and effort by connecting a single record in one parent table to records in a child table.

Why use a relationship?

For example, when entering a new company, it would be nice to enter all the contacts who work there. Or, when entering a new contact, to also pick the company they're with. You can use a relationship to do this. Instead of creating new fields for company info on the contacts form, you can simply connect info from a Companies table right into the Contacts form. And you can summarize all the info from Contacts to display in Companies, for instance, the number of Contacts.

Almost every Quick Base app has several relationships.

When you relate two tables you:

  • Save time because you don’t need to enter the same data over and over in separate tables.

  • Reduce the risk of data-entry errors.

  • Can summarize data from a related table.

You can create a relationship between any two tables. Most often these tables reside within the same application, but you can create a relationship with a table in a separate application too.

One-to-many relationships

When you create a relationship, you’re telling Quick Base to connect a single record in one table to many records in the other table. This is called a one-to-many relationship.

In a relationship in Quick Base, the table on the "one" side is the parent table and the table on the "many" side is the child table.

It’s easy to think of real-world examples of one-to-many relationships:

  • A business has many locations.

  • A project has many tasks.

  • One manager has several employees.

Using the companies and contacts example from above:

If there is a one-to-many relationship between a Companies table and a Contacts table, when your employee enters a new contact, the new contact info can be related to an existing customer.

The company name and related info come directly from the Companies table.

For each additional contact, there’s never a need to re-enter company info. And if the company info changes, it’s automatically updated in all the related contact records.

What makes relationship work

When you create a relationship between two tables, Quick Base performs some additional "magic" behind the scenes that transforms each table in useful ways.

For example, after you relate a Contacts table to a Companies table, you can see a list of contacts for that company and enter new contacts directly from the Company form.

Another bit of magic you can perform with relationships is to summarize information from the child records on the parent record, for example, you can summarize the number of contacts for each customer.

Fields used with relationships

To power this magic, every Quick Base relationship includes a reference field. Quick Base creates this field in the child table. It identifies the related parent record. To keep everything organized, Quick Base uses the parent table's key field to populate the child table's reference field. The key field always contains a unique value.

Because the reference field contains the parent record's key field, it usually displays as a number. For instance, instead of "Project Name" users would see the Project ID#. If this happens in your app, you can designate a reference proxy field and use that field in reports and on forms.

When you create a relationship between two tables, additional fields include:

  • Lookup fields. These appear in the child table and provide more information about a linked record in the parent table. For example, if you want to include additional information on an project within the tasks table, such as project manager name or start and end dates. Learn how to create a lookup field.

  • Report link fields appear in the parent table. These display as links. When you choose a report link, you see the related records from the child table. Report links help you get more information between tables quickly. For example, if you were to choose an project's tasks link you'd see a list of all the tasks for that project.

  • URL (formula) fields appear in the parent table, and are displayed as buttons on forms and reports. These buttons let you add a new record to the child table from within the parent table. The new record will automatically be related to the parent record. For example, if you choose an Add Task button in a project record, the new task record appears with the project already selected.

  • Summary fields appear in the parent table and display data from the child table. Most often, summary fields calculate totals. For example, a Projects parent table could include a summary named Number of Tasks. This field calculates the number of tasks in the Tasks child table that are linked with each project. Summary fields can also include other information, such as showing the record with earliest start date. Learn how to create a summary field.

The power of table-to-table relationships

As you learn more about table-to-table relationships, you’ll start to see the remarkable power of this concept and how it can help you create apps that can facilitate complex work flows and business logic.

Here's some additional ideas to illustrate how related tables provide you with benefits for your apps:

  • Keep in mind that while a relationship is created between two tables, it’s actually the records in the tables that form the relationship. So, for example, each Company record has many Contact records.

  • A parent table can have more than one child table. A company can have many contacts, but also many activities, and many documents.

  • A table can be on both sides of a relationship. For example, a country can be the parent to many states, and a state can be the parent to many cities. In this case, a States table is both a parent table and a child table.

Related topics: