Understanding References Between Tables

Introduction

Once you have an app with multiple tables, it is often useful to create connections or "References" between the app's tables.

For example, an order capture application might contain the following tables:

  1. Customers table having one record per customer.
  2. Products table having one record per product being sold.
  3. Orders table having one record per order that is taken.
  4. Order Details table having one record per line item in its parent order.

The order capture application might contain the following references:

  1. Each Orders record will reference the Customers record of the customer who placed that order.
  2. Each Order Details record will reference its parent Orders record.
  3. Each Order Details record will reference its corresponding Products record.

References serve three purposes:

  1. They allow you to represent relationships. For example, the reference between the Orders record and the Customers record allows you to capture the relationship between an order and the customer who placed that order.
  2. They allow you to easily retrieve information from a related record in another table. For example, the reference between the Order Details record and the Products table allows you to start from an individual Order Details record and retrieve the name, image, and price fields from the related Products record.
  3. They allow you to navigate from one record to another. For example, you might retrieve a particular Order Detail record then navigate to the related Orders records, and then to the related Customers records.

 

Adding a Reference

You create a reference by adding a Ref type field to a table and specifying the related table's name. For example, in the Orders table you would add a Ref to the Customers table. In the Order Details table you would add one Ref to the Orders table and another to the Products table.

 

Reverse References

For each Ref you add, the system automatically adds a reverse reference in the opposite direction. For example, when you add the Ref from the Orders table to the Customers table, the system automatically adds a reverse reference from the Customers table to the Orders table.

Reverse References serve three purposes:

  1. They allow you to navigate from one record to all of its related records.
  2. They allow the user interface to easily display a record along with all of its related records in another table.
  3. They allow aggregates to be computed like the count of a customer's orders or the total dollar value of a customer's orders. 

 

References and Reverse References Contain Key Values

A Ref type field always stores the key value of the referenced record. For example, if the key value of a Customers record is "Ann Adams" then the Ref field in the related Orders record will contain the value "Ann Adams". A table's key value uniquely identifies each record in that table. The copy of the key value in the Ref field allows the system to unambiguously retrieve the proper record in the referenced table. 

The system-added reverse reference field is a list of the key values of the related records. For example, the reverse reference in Customers record "Ann Adams" would contain "1003", "1005", and "1010" if those were the key values of Ann's related Orders records.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.