Version: 6.5.x and 6.7.x

When designing tables for a relational database application, fields are grouped into tables in such a way as to avoid duplication of data. This is known as the process of data normalization, a topic for another time, but it might be summarized by saying that "the prime directive" for table design is to avoid redundant data.

However, if we consider the example of an invoicing system, you will notice that the Primary key field of the Customers table, Customer ID, is duplicated in the Invoices table.

In an invoicing system, properly designed or normalized data structures would yield a Customers table and an Invoices table. In that system, we would avoid duplicating customer information in the Invoices table; customer data would ideally only be located in the Customers table.

But when an invoice record is accessed, we do expect to see information from a customer record such as the company name, the person's name, and perhaps their phone number on the invoice.

We expect to see customer information when we look at an invoice because there is an inherent logical relationship between a customer and an invoice; that is, a customer purchase results in an invoice being created.

For a database system to access related data, we must make this inherent, implied relationship explicit. To make the relationship explicit, we first determine the nature of the relationship by figuring out which table will supply one record's information to many records in the other table. In other words, would a single customer's information be available to one invoice, or many invoices? Clearly a customer could make many purchases, resulting in many invoices. So many invoices might be related to one customer, and one customer might be related to many invoices. In such a relation, the customers table is known as the One table and the invoices table is known as the Many table. Having reached this understanding, the relationship is made explicit by duplicating the Primary Key field of the One table, the Customers ID field, and in the Many table, Invoices.

The Customer ID field is placed in the invoice only to provide the ability to look up the related customer record, so it is a key value that represents information that is foreign to the invoice table. This copy of the primary key from the One table that is placed in the Many table is known as a foreign key in the Many table.

4th Dimension can use this foreign key embedded within the invoice, to look up or access the related customer record with the matching primary key value.

In 4D, we implement the looking up of the record by drawing an arrow that points from the foreign key in the Many table, to the Primary key in the One table, the table supplying one record's information.