Progress
Language Tutorial
for Character


Understanding Table Relationships

The most powerful advantage an electronic filing system has over a paper filing system is the ability to process data based on the relationships of the database tables to each other. When a database uses table relationships in this way, it is called a relational database.

Database table relationships are called relations. Two records from different database tables are said to have a relation if each contains a field with identical data, as shown in Figure 9–1.

Figure 9–1: Table Relations

Two fields in two different tables contain the same data. Although the field names in Figure 9–1 are identical, they don’t have to be to form a relation. Relations are the result of good database design. When you read the Progress Database Design Guide , you’ll find a lot of information about normalization through the development of relations. Normalization is the database design principle that seeks to eliminate redundant data. In Figure 9–1, notice that the Order record does not contain the name or address of the customer. It doesn’t need to because that information is already stored in the Customer record. However, you do need one field to associate this particular order with a particular customer. The Customer Number field serves this purpose. Now you’ve minimized the redundant data and established a common link between the two tables.

It’s important to understand that normalizing your database in this manner does not create relationships. Relationships are created programmatically. However, good database design sets the preconditions for effective relationships.

Table records can have several types of relations, but for the most part, programmers work with two: one-to-one relationships and one-to-many relationships. Relationship types describe the way a particular record in one table can relate to one or more records in another table.

The first relationship type is called a one-to-one relationship and occurs when one record can relate to only one instance of a record in another table. For example, each customer order contains one or more order lines. An order line is a request for a quantity of a particular item. Therefore for each order line, there is only one inventory item, as shown in Figure 9–2.

Figure 9–2: One-to-one Relationship

When one record can relate to many records in another table, it is called a one-to-many relationship. For example, each customer can have several orders. In other words, each customer number can occur only once in the Customer table, but can occur many times in the Order table. This relationship is shown in Figure 9–3.

Figure 9–3: One-to-many Relationship

You may also suspect that there is a many-to-one relationship. However, a many-to-one relationship is just an inverted one-to-many relationship. In other words, instead of saying one customer can have many orders, you can say many orders relate to one customer. Both statements define a one-to-many relationship.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095