This chapter is from the book
This chapter is from the book
This chapter is from the book
Types of Relational Models
Depending on the data you're working with, you can set up one of several relational database models. In each of these models, however, you need to differentiate between a child table (also called a dependent table or a controlled table) and a parent table (also called a primary table or a controlling table). The child table is the one that is dependent on the parent table to fill in the definition of its records. The Contacts table, for example, is a child table because it is dependent on the Companies table for the company information associated with each person.
The One-To-Many Model
The most common relational model is one where a single record in the parent table relates to multiple records in the child table. This is called a one-to-many relationship. The sales leads example is a one-to-many relationship because one record in the Companies table can relate to many records in the Contacts table (in other words, you can have multiple sales contacts from the same firm). In these models, the "many" table is the one where you add the foreign key.
Another example of a one-to-many relationship is an application that tracks accounts- receivable invoices. You need one table for the invoice data (Invoices) and another for the customer data (Customers). In this case, one customer can place many orders, so Customers is the parent table, Invoices is the child table, and the common field is the Customer table's primary key.
The One-to-One Model
If your data requires that one record in the parent table be related to only one record in the child table, you have a one-to-one model. The most common use of one-to-one relations is to create separate entity classes to enhance security. In a hospital, for example, each patient's data is a single entity class, but it makes sense to create separate tables for the patient's basic information (such as the name, address, and so on) and his or her medical history. This enables you to add extra levels of security to the confidential medical data (such as a password). The two tables then become related based on a common "PatientID" key field.
Another example of a one-to-one model is employee data. You separate the less-sensitive information such as job title and startup date into one table, and restricted information such as salary and commissions into a second table. If each employee has a unique identification number, you use that number to set up a relationship between the two tables.
Note that in a one-to-one model, the concepts of child and parent tables are interchangeable. Each table relies on the other to form the complete picture of each patient or employee.
The Many-to-Many Model
In some cases, you might have data in which many records in one table can relate to many records in another table. This is called a many-to-many relationship. In this case, there is no direct way to establish a common field between the two tables. To see why, let's look at an example from a pared-down accounts-receivable application.
Table 3.6 shows a simplified structure of an Invoices table. It includes a primary key—InvoiceID—as well as a foreign key—CustomerID—from a separate table of customer information (which I ignore in this example).
Table 3.6 The Structure of an Invoices Table
|
Field |
Description |
InvoiceID |
The primary key. |
CustomerID |
The foreign key from a table of customer data. |
Table 3.7 shows a stripped-down structure of a table of product information. It includes a primary key field—ProductID—and a description field—Product.
Table 3.7 The Structure of a Products Table
|
Field |
Description |
ProductID |
The primary key. |
Product |
The product description. |
The idea here is that a given product can appear in many invoices, and any given invoice can contain many products. This is a many-to-many relationship, and it implies that both tables are parents (or, to put it another way, neither table is directly dependent on the other). But relational theory says that a child table is needed to establish a common field. In this case, the solution is to set up a third table—called a relation table—that is the child of both the original tables. In the ongoing example, the relation table contains the detail data for each invoice. Table 3.8 shows the structure of such a table. As you can see, the table includes foreign keys from both Invoices (InvoiceID) and Products (ProductID), as well as a Quantity field.
Table 3.8 The Structure of a Table of Invoice Detail Data
|
Field |
Description |
InvoiceID |
The foreign key from the Invoices table. |
ProductID |
The foreign key from the Products table. |
Quantity |
The quantity ordered. |