Home > Designing and creating databases > Working with related tables and files > About relationships

About relationships

A relationship is a powerful method for organizing your data. Using a relationship, you can join data in one or more tables based on common field values, different field values, or a comparison of values in two or more fields.

After you create a relationship, you can do either of the following to display the data from the related table:

•

Design a relational database, which is one or more tables in one or more files that, when used together, contain all the data you need for your work. Each occurrence of data is stored in only one table at a time but can be accessed and displayed from any related table. You can change any occurrence of your related data, and the changes appear in all places where that related data is used.

•

Define a lookup to copy data from a related table into a field in the target table. The copied data is now stored in two places, just as if it were copied and pasted into a target field. Looked-up data is current at the time it is copied, but once copied it remains static unless it is relookedup or the lookup is triggered again.

Important  Whenever you want to use data from another table — either in a relational database or for a lookup — you must first define a relationship between the two tables.

For example, a typical Sales database may have these tables: an Invoices table, which keeps a record of each invoice; a Products table, which stores the products and their current prices; and a LineItems table, which stores sales data for each line of the invoice, including the item being sold, the quantity, and the price at which it is sold. Because invoices are a mix of dynamic and static data, you use both related fields and lookups to display your data. Records from the LineItems table are displayed dynamically, in a portal on the Invoices layout, but the actual sales price of each line item is entered using a lookup, so the invoice totals remain the same, even if prices change at some future date.

You create a relational database by defining a relationship between two fields, called match fields. These fields can be in different tables or they can be in the same table (a self-join). You are able to access related data when the value in the match field(s) on one side of the relationship compares successfully with a value in the match field(s) on the other side of the relationship, according to the criteria you establish in the relationship.

After you have created a relationship you can use fields from the related table just as you would use any fields in the current table: to display data on a layout, as part of a calculation formula, in a script, as a match field for another relationship, and so on.

When you display related data in a portal, values from all related records are displayed, subject to a user’s access privileges. When the related field isn't in a portal, the value from the first related record is displayed. See Creating portals to display related records.

Note  Use access privileges in the source table to limit or prevent access to related data. For example, users must have access privileges to view a related match field to see the related fields from that relationship. See Creating and managing privilege sets.

You can sort related records before displaying them. When you place a related field directly on a layout, you see the value from the first related record in the sort order (such as the lowest or highest value). When you display related fields in a portal, the related records are displayed in the sort order assigned to the portal, which takes precedence over any sort order in the relationship definition.

Notes

•	You can base relationships on one or more match fields in each table. Match fields should be the same type, for example, number fields or a calculation field that returns a numeric result.

•

Relationships are always created between two tables, or, in the case of self-joining relationships, two occurrences of the same table in the relationships graph. You can connect relationships together in a series and access related data from any point within that series, but each relationship is created between only two tables.

•	Two or more related tables cannot form a cycle. Each series of relationships must have a beginning table and an ending table.

•	The number of relationships is limited only by disk space.

•	Relationships are bi-directional, although you can set independent record sorting, creation, and deletion options for each table in the relationship.

•	You can add pairs of match fields to a relationship as necessary until you are able to establish the relational criteria you want.

•	Because a sort order can be assigned to a portal, you can display data from a single relationship in multiple portals on the same page, and use a different sort order for each portal.

•	Before you begin building a relational database, it’s a good idea to plan it on paper first. See About planning a database.

Related topics 

About the types of relationships

Creating relationships