About relationshipsA 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.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.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.
• Before you begin building a relational database, it’s a good idea to plan it on paper first. See About planning a database.