One-to-Many Relationships
| The one-to-many relationship is by far the most common and most important relationship type. Almost any relational database offers numerous examples of one-to-many relationships. In this section, I provide two examples of one-to-many relationships using the order-entry model, and two using the Classic TV database you worked with in Chapter 2. This way, you'll have plenty of opportunity to see one-to-many relationships at work. An Order-Entry ModelLet's start with two examples from a typical order-entry model, such as the Northwind sample database. As a wholesaler of camping equipment, you have a table for customer contact information (Table 3.1); it has data about the subject customers. You also have a table for orders (Table 3.2); it has data about the subject orders. Each row in the Customers table represents one customer; each row in the Orders table represents a single order.
Each customer is uniquely identified by the CustomerID, which is the primary key of the Customers table. Each customer can appear only once in the Customers table. The Orders table also has a field called CustomerID that tells you which customer placed the order. Any CustomerID in the Orders table must also be a CustomerID in the Customers table. In the Orders table, CustomerID is the foreign key. Database designers use various naming conventions, but the foreign key usually has the same name or a similar name as the primary key from which it was copied. Any particular CustomerID can appear only once in the Customers table, but any CustomerID can appear many times in the Orders table. That makes sense because the same customer can have many orders. Put another way, a single customer can have many orders, but no order can have more than one customer. You can establish a one-to-many relationship between the Customers table and the Orders table through the CustomerID field, the field with matching data. CustomerID is the primary key in the primary table, the table on the "one" side of the relationship. CustomerID is the foreign key in the related table, the table on the "many" side of the relationship. Let's do one more example before heading to the Classic TV database. Consider the Categories and Products tables in Tables 3.3 and 3.4.
The Categories table contains data about a subjectin this case, categories. Each row in the Categories table represents a single category. The CategoryID is the primary key of the Categories table and uniquely identifies each category. The Products table contains data about the subject products. Each row represents a single product. Each product is uniquely identified by the ProductID. The Products table also has a CategoryID field, which contains only categories that are included in the Categories table. Each category can appear only once in the Categories table, but the same category can appear many times in the Products table. The two tables have matching data in the CategoryID fields. You can establish a one-to-many relationship between the Categories and Products tables. The Categories table is on the "one" side of the relationship, where the CategoryID is the primary key in the primary table. The Products table is on the "many" side of the relationship, where CategoryID is the foreign key in the related table.
Classic TV DatabaseLet's turn to the Classic TV database, whose current state of development is shown in Table 3.5.
Think about how the Networks and Programs tables relate to one another. Each program ran on only a single television network. (I'm talking about the original airing of the show in prime time, not the many channels that might now carry its reruns.) On the other hand, a single network can air many programs. The Networks and Programs tables have a one-to-many relationship. One of your mission objectives is to know the broadcast history of a program, so you certainly want to be able to combine data from the two tables. But how can you do that? You need to associate the Networks table with the Programs table so you can integrate the data from both tables when needed. If you look at the current field lists, however, no field in the Programs table has anything to do with the Networks table. You need to add a field to the Programs table from the Networks table (see Table 3.6) that will enable you to combine data from both tables. That field must uniquely identify and represent each row of data in the Networks table. That field, as you know by now, is NetworkID, the primary key of the Networks table.
By adding the primary key of the Networks table to the Programs table, you have the potential of bringing any of the values from the Networks table (Popular Name, Official Name, Founder, Notes) together with any values from the Programs table. NetworkID is the primary key in the primary table Networks, the table on the "one" side of the relationship. NetworkID is also the foreign key in the related table Programs, the table on the "many" side of the relationship (see Table 3.7). The two keys have matching data through which you can establish a one-to-many relationship.
Let's look at another one-to-many relationship in the Classic TV database. Earlier I described a relationship between the Categories and Products tables, where each product was assigned a single category. Look at the tables and fields in Table 3.5. Do any tables in the Classic TV database have a relationship similar to that of Categories and Products? Consider the relationship between Genres and Programs. Each row in the Genres table represents one category, or genre, of show; each show in the Programs table is described by a single genre. A single genre can be used to describe many programs, but each program can have only one genre. (You might argue that some TV shows overlap genres, but let's assume just one genre per show.) Potentially, the Genres and Programs tables have a one-to-many relationship. But how do you record the genre of each program? Currently, there is no field in the Programs table you can use to assign a genre. Again, you need to add a field to the Programs table from the Genres table that will enable you to combine data from both tables when you need it. That field will uniquely identify and represent each row of data in the Genres table. The field you want is GenreID, the primary key of the Genres table. By adding the primary key of the Genres table to the Programs table, you can join any of the values in the Genres table with any values from the Programs table. GenreID is the primary key in the primary table of Genres, which is on the "one" side of the relationship, and GenreID is also the foreign key in the related table of Programs on the "many" side of the relationship (see Table 3.8).
Table 3.9 shows the current status of the Classic TV database.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 169