A one-to-many relationship is the most common type of relationship, since all many-to-many relationships must be broken down into two one-to-many relationships. When a one-to-many relationship occurs, place the primary key on the table at the one end of the relationship as a foreign key on the table on the many end of the relationship. For example, since one customer may have many orders, place the customer number on the order record.
The design of Web pages, displays, or reports that include information from only one record of the many relationship, along with information from the one end of the relationship, is easy to construct. The display will not have any repeating information. An example is an order inquiry using an order number to look up a single order. Since the order is for one customer, the result would be fields from the order and a single customer.
Designing the reverse is more complicated, since the table at the one end of the relationship may have many records for the many end. These are implemented in a variety of ways. For a simple display screen, the information from the one end is displayed with a repeating number of groups of information from the many end of the relationship. In Microsoft Access, this might be a form with a subform, such as a customer with a subform of all the customer’s orders. If there was a large number of records from the many end, scroll bars would appear.
In simple situations, the relationship might also be implemented by using a drop-down list, with each record from the many end becoming one entry in the one end; an example is the display of a car along with a drop-down list containing all the models for the car. When designing Web sites, the information from the one end might be at the top of the page, with multiple groups of data below it or multiple links to the data. An example is one search engine topic resulting in many matching links or one genre of music and many artists that match the genre.
Many-to-Many Relationship
When the relationship is many-to-many, three tables are necessary: one for each data entity and one for the relationship. The ORDER and ITEM entities in our example have a many-to-many relationship. The primary key of each data entity is stored as a foreign key of the relational table. The relational table may simply contain the primary keys for each data entity or may contain additional data, such as the grade received for a course or the quantity of an item ordered. Refer to the table layout illustrated in Figure 13.24. The ORDER ITEM table contains information about which order contains which items, and provides a link between the ORDER table and the ITEM MASTER table.
The relationship table should be indexed on each foreign key—one for each of the tables in the relationship—and may have a primary key consisting of a combination of the two foreign keys. Often corporations will use a unique key, such as sequence number, as the primary key for the relational table. To find many records from a second table given the first table, directly read the relational table for the desired key. Locate the matching record in the second many table. Continue to loop through the relational table until the desired key is no longer found. For example, to find records in the ITEM MASTER for a specific record in the ORDER table, directly read the ORDER-ITEM table using the ORDER-NUMBER as the index. Records are logically sequenced based on the data in the index, so all records for the same ORDER-NUMBER are grouped together. For each ORDER-ITEM record that matches the desired ORDER-NUMBER, directly read the ITEM MASTER table using the ITEM-NUMBER as an index.
The logic is the same for the reverse situation, such as finding all the orders for a backordered item that has been received. Use the desired ITEM-NUMBER to read the ORDER-ITEM table directly. The ORDER-ITEM index is set to the ITEM-NUMBER. For all matching ORDER ITEM records, use the ORDER-NUMBER to read the ORDER table directly. Finally, read the CUSTOMER MASTER table directly to obtain the CUSTOMER-NAME and ADDRESS using the CUSTOMER-NUMBER on the ORDER table.
Relational tables may have relationships to more tables in the database than just the two to which they directly connect. For example, there might be a relational table called Class or Section to link students and courses, since each student may take many courses and each course may have many students. The Section table may have a relationship to the Textbook or to the Instructor for that section.
