Identification of Relationships

Now that you've divided your information into tables, you need a way to tell Visual FoxPro how to bring it back together again in meaningful ways. For example, the following form includes information from several tables.

The Order Entry form uses information from several tables.

Visual FoxPro is a relational database management system. That means you store related data in separate tables. Then you define relationships between the tables and Visual FoxPro uses the relationships to find associated information stored in your database.

For example, suppose that you want to phone an employee with questions about a sale the employee made. Employee phone numbers are recorded in the Employee table; sales are recorded in the Orders table. When you tell Visual FoxPro which sale you're interested in, Visual FoxPro can look up the phone number based on the relationship between the two tables. It works because Employee_id, the primary key for the Employee table, is also a field in the Orders table. In database terminology, the Employee_id field in the Orders table is called a foreign key, because it refers to a primary key from a different, or foreign, table.

Employee_id field as primary key for Employee table and foreign key for Orders table

So, to set up a relationship between two tables — Table A and Table B — you add one table's primary key to the other table, so that it appears in both tables. But how do you decide which table's primary key to use? To set up the relationship correctly, you must first determine the nature of the relationship. There are three types of relationships between tables:

• One-to-many relationships
• Many-to-many relationships
• One-to-one relationships

The rest of this section presents an example of each type of relationship and explains how to design your tables so that Visual FoxPro can associate the data correctly. The purpose of each example is to explain how you determine the relationships between your tables and how you decide which fields belong in the tables to support those relationships — it doesn't describe how to use the Visual FoxPro interface to relate tables.

Example:

A one-to-many relationship is the most common type of relationship in a relational database. In a one-to-many relationship, a record in Table A can have more than one matching record in Table B, but a record in Table B has, at most, one matching record in Table A.

For example, the Category and Products tables in the Tasmanian Traders database have a one-to-many relationship.

The Category and Products tables represent a one-to-many relationship.

To set up the relationship, you add the field or fields that make up the primary key on the "one" side of the relationship to the table on the "many" side of the relationship. You use a primary or candidate index key for the "one" side of the relationship, and a regular index key for the "many" side. In this case, you would add the Category_id field from the Category table to the Products table, because one category includes many products. Visual FoxPro uses the category ID number to locate the correct category for each product.

For information about creating index keys, see Working with Tables.

Example:

In a many-to-many relationship, a record in Table A can have more than one matching record in Table B, and a record in Table B can have more than one matching record in Table A. This type of relationship requires changes in your database design before you can correctly specify the relationship to Visual FoxPro.

To detect many-to-many relationships between your tables, it's important that you look at both directions of the relationship. For example, consider the relationship between orders and products in the Tasmanian Traders business. One order can include more than one product. So for each record in the Orders table, there can be many records in the Products table. But that's not the whole story. Each product can appear on many orders. So for each record in the Products table, there can be many records in the Orders table.

The Orders and Products tables represent a many-to-many relationship.

The subjects of the two tables — orders and products — have a many-to-many relationship. This presents a challenge in database design. To understand the problem, imagine what would happen if you tried to set up the relationship between the two tables by adding the Product_id field to the Orders table. To have more than one product per order, you need more than one record in the Orders table per order. You'd be repeating order information over and over for each record that relates to a single order — an inefficient design that could lead to inaccurate data. You run into the same problem if you put the Order_id field in the Products table — you'd have more than one record in the Products table for each product. How do you solve this problem?

The answer is to create a third table that breaks down the many-to-many relationship into two one-to-many relationships. This third table is called a junction table, because it acts as the junction between two tables. You put the primary key from each of the two tables into the junction table.

The Order_Line_Items table creates a one-to-many link between Orders and Products.

A junction table might hold only the two primary keys from the tables it links together or, as in the Order_Line_Items table, the junction table might hold additional information.

Each record in the Order_Line_Items table represents one line item on an order. The Order_Line_Items table's primary key consists of two fields — the foreign keys from the Orders and Products tables. The Order_id field alone doesn't work as the primary key for this table, because one order can have many line items. The order ID is repeated for each line item on an order, so the field doesn't contain unique values. The Product_id field alone doesn't work either, because one product can appear on many different orders. But together the two fields in the junction table always produce a unique value for each record. The junction table does not require its own primary key.

In the Tasmanian Traders database, the Orders table and the Products table aren't related to each other directly. Instead, they are related indirectly through the Order_Line_Items table. The many-to-many relationship between orders and products is represented in the database using two one-to-many relationships:

• The Orders and Order_Line_Items tables have a one-to-many relationship. Each order can have more than one line item, but each line item is connected to only one order.
• The Products and Order_Line_Items tables have a one-to-many relationship. Each product can have many line items associated with it, but each line item refers to only one product.

Example:

In a one-to-one relationship, a record in Table A can have no more than one matching record in Table B, and a record in Table B can have no more than one matching record in Table A. This type of relationship is unusual and might call for some changes in your database design.

One-to-one relationships between tables are unusual because in many cases, the information in the two tables can simply be combined into one table. For example, suppose you created a table, called Ping-Pong Players, to track information about a Tasmanian Traders Ping-Pong fundraising event. Because the ping-pong players are all employees of Tasmanian Traders, this table has a one-to-one relationship with the Employee table in the Tasmanian Traders database.

The Employee and Ping_Pong_Players represent a one-to-one relationship.

You could add all the fields from the Ping-Pong Players table to the Employee table. But the Ping-Pong Players table tracks a one-time event, and you won't need the information after the event is over. Additionally, not all employees play Ping-Pong, so if these fields were in the Employee table, they would be empty for many records. For these reasons, it makes sense to create a separate table.

When you detect the need for a one-to-one relationship in your database, consider whether you can put the information together in one table. For example, in the Employee table, one employee can have one manager, who is also an employee. You can add a field for the manager's id number. To pull the information together later, you can use a self join in your query or view. You don't need a separate table to resolve the one-to-one relationship. If you don't want to do that for some reason, here's how to set up the one-to-one relationship between two tables:

• If the two tables have the same subject, you can probably set up the relationship by using the same primary key field in both tables.
• If the two tables have different subjects with different primary keys, choose one of the tables (either one) and put its primary key field in the other table as a foreign key.

See Also

Determination of the Fields You Need | Refinement of the Design | Organization of Requirements into Tables | Analysis of Data Requirements | Designing Databases | Creating Databases | Working with Tables