Share this Page URL

Chapter 3. Working with Multiple-Table Q... > Types of Relational Models - Pg. 54

Working with Multiple-Table Queries 54 Types of Relational Models Depending on the data you're working with, you can set up one of several relational database mod- els. 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 control- ling 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 Com- panies 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 infor- mation 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 InvoiceID CustomerID Description The primary key. The foreign key from a table of customer data.