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 Model

Let'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.

Table 3.1. Table of Customer Contact Information

CustomerID (PK)	Company Name	Street Address	City	State	ZIP	<<other fields>
001	Outdoor Emporium	1165 C Street	Arkadelphia	AK	71998
002	Mountain Mart	14098 Hopyard	Mayfield	MI	49666
003	Travelers Aid	2 West 19 Street	Sullivan	NH	03445
004	The Hiker	673 Ignatius Way	Vanderbilt	MI	49795
005	Gleason's Store	82-17 Francisco	Fargo	ND	58109
PK=Primary key

Table 3.2. Table of Order Data

OrderID (PK)	CustomerID (FK)	Order Date	Ship Date	Shipper	<<other fields>>
10075	002	10/16/2004	10/18/2004	Acme
10076	003	10/19/2004	10/21/2004	Empire
10077	002	10/21/2004	10/23/2004	USPS
10078	001	10/21/2004	10/24/2004	USPS
10079	002	10/22/2004	10/27/2004	Acme
PK=Primary key; FK=Foreign key

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.

Table 3.3. The Categories Table Contains Data About Product Categories

CategoryID (PK)	Category	Description
1	Men's Apparel	Outerwear worn by men, such as shirts, sweaters, slacks
2	Women's Apparel	Outerwear worn by women, such as blouses, dresses, pants
3	Home Furnishings	Household items, such as bedding, kitchen appliances, rugs
4	Accessories	Items such as watches, luggage, toiletries
5	Footwear	Outerwear for feet, such as sneakers, slippers, hiking boots
PK=Primary key

Table 3.4. The Products Table Contains Data About Products.

ProductID (PK)	Product Name	CategoryID (FK)	Supplier	<<otherfields>>
00123	Hiking Socks	5	Mountain Footwear
00124	Duffle Bag	4	In The Bag, Inc.
00125	Garment Bag	4	Luggage 'n More
00126	Shoulder Tote	4	Successful Accessories
00127	Hiking shorts	2	Garmendi Garments
PK=Primary key; FK=Foreign key

The Categories table contains data about a subject—in 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.

Q&A Q1: You said that each value in the foreign key of the related table must appear in the primary key of the primary table. But how do you make sure that values that aren't in the primary table aren't entered into the related table? A1: You'll see how that's done in the next chapter, where you will formally establish the relationship between the two tables. I admit that it's a little confusing: Although I've written “the table on the 'many' side of the relationship,” you haven't yet formally created the relationship in the Relationships window. Don't get hung up on this—the important thing is to learn o recognize the various relationship types and how they work.

Classic TV Database

Let's turn to the Classic TV database, whose current state of development is shown in Table 3.5.

Table 3.5. The Current Status of the Classic TV Database

Current Field Lists
Programs	Genres	Networks	Actors	Roles
ProgramID PK	GenreID PK	NetworkID PK	ActorID PK	ProgramID
Name	Genre	Official Name	First Name	First Name
Year Started	Description	Popular Name	Last Name	Last Name
Year Ended		Founder	Gender	Occupation
Synopsis		Notes	Biography
Location
Notes
PK=Primary key

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.

Table 3.6. The Networks Table Is on the “One” Side of the One-to-Many Relationship with Programs

NetworkID(PK)	Official Name	Popular Name	Founder	Notes	<<otherfields>
1	Columbia Broadcasting System	CBS	William Paley	CBS wasthe…
2	National Broadcasting Company	NBC	David Sarnoff	NBC startedas…
3	American Broadcasting Company	ABC	Leonard Goldenson	In the earlydays…
4	Fox Television	FOX	Rupert Murdoch	Barry Diller, along with…
PK=Primary key

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.

Table 3.7. The NetworkID Field from the Networks Table Is Included in the Programs Table as a Foreign Key. Because the Two Tables Now Have Matching Data, You Can Establish a One-to-Many Relationship Between Them.

ProgramID (PK)	Program	NetworkID (FK)	Year Started	Year Ended	<<otherfields>>
1	The Andy Griffith Show	1	1960	1968
2	Happy Days	3	1974	1984
3	The Bob Newhart Show	1	1972	1978
4	Newhart	1	1982	1990
5	Sanford & Son	2	1972	1977
PK=Primary key; FK=Foreign key

Q&A Q1: You're wrong about a program airing on only one TV station during its run in prime time. I distinctly remember that at some point the courtroom drama Matlock with Andy Griffith stopped running on NBC and moved to ABC. A1: You're right. Occasionally a program will air on one network and move to another network. I should have written that no program can run on the same network more than once at the same time. For now, let's keep things simple by including only the network that originated the show. That's only half a cop-out: The originating network developed the show, and a later move to another network might be of only minor interest. In the section on many-to-many relationships a few pages ahead, I'll give you a detailed explanation of how to include the full broadcast history.

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.8. The GenreID Field from the Genres Tables Has Been Added to the Programs Table as a Foreign Key. Note That, Although I Placed the GenreID Column Next to the Program Field, This Is Simply for Presentation. The Field Could Be in Any Position; It Does Not Necessarily Follow Program.

Genres
GenreID (PK)	Genre	Description
1	Rural Comedy	Primarily rural setting and characters, such as The Andy Griffith Show
2	General Drama	General category for drama; excludes police and hospital dramas
3	Urban Comedy	Primarily urban setting, themes, and characters, such as The Bob Newhart Show
4	General Comedy	General category for all other comedies
5	Police Drama	Primary characters and themes center on police work, such as Hill Street Blues

Programs
ProgramID (PK)	Program	GenreID (FK)	NetworkID (FK)	<<otherfields>
1	The Andy Griffith Show	1	1
2	Happy Days	4	3
3	The Bob Newhart Show	3	1
4	Newhart	1	1
5	Sanford & Son	4	2
PK=Primary key; FK=Foreign key

Table 3.9 shows the current status of the Classic TV database.

Table 3.9. The Current Field List for the Classic TV Database After Adding Foreign Keys to the Programs Table

Programs	Genres	Networks	Actors	Roles
ProgramID PK	GenreID PK	NetworkID PK	ActorID PK	ProgramID
GenreID FK	Genre	Official Name	First Name	First Name
NetworkID FK	Description	Popular Name	Last Name	Last Name
Name		Founder	Gender	Occupation
Year Started		Notes	Biography
Year Ended
Synopsis
Location
Notes
PK=Primary key; FK=Foreign key