Final List of Fields and Relationships

Table 3.26 shows the final list of tables and fields for the Classic TV database. The method I described for developing it took shortcuts and half-measures. In a real-world situation, you would want to take additional measures to ensure that you had collected all the fields you needed and that they were sufficient to accomplish your mission objectives.

Table 3.26. The Final List of Tables and Fields for the Classic TV Database

Programs	Broadcasts	Genres	Networks	Actors	Roles
ProgramID PK	ProgramID CPK/FK	GenreID PK	NetworkID PK	ActorID PK	ProgramIDCPK/FK
GenreID FK	NetworkID CPK/FK	Genre	OfficialName	First Name	ActorID CPK/FK
Name	Year Started	Description	PopularName	Last Name	First Name
Location	Year Ended		Founder	Gender	Last Name
Synopsis			Notes	Biography	Occupation
Notes					Year Started
					Year Ended
					Notes
PK=Primary key; CPK=Composite primary key; FK=Foreign key

This final list does not represent some state of perfection. Certainly, alternative roads could have been taken. But I've given you some idea of the process of developing field lists and tables, as well as determining primary and foreign keys.

Table 3.27 shows all the relationships of the Classic TV database at the beginning of the chapter. You can use such a scheme to go through each relationship one by one, resolving those relationships that need to be resolved while considering alternative design solutions.

Table 3.27. The Relationships of the Classic TV Database at the Start of the Chapter

	Programs	Genres	Networks	Actors	Roles
Programs		1:M	M:M	M:M	M:1
Genre	M:1
Networks	M:M
Actors	M:M				M:M
Roles	1:M			M:M

You'll notice that the scheme contains many-to-many relationships. You resolved these relationships during the chapter by creating linking tables that include foreign keys from the original tables. The current relationships in the database shown in Table 3.28 thus include no many-to-many relationships, even though, for descriptive purposes, they still theoretically exist. In the next chapter, you will formally establish the one-to-many relationships shown in Table 3.28.

Table 3.28. The Relationships of the Classic TV Database at the End of the Chapter

	Programs	Genres	Networks	Actors	Roles	Broadcasts
Programs		1:M			M:1	M:1
Genre	M:1
Networks						M:1
Actors					M:1
Roles	1:M			1:M
Broadcasts	1:M		1:M

In the chart, the horizontal axis represents the table's relationship to the table on the vertical axis. For example, viewing the relationship between the Genres and Programs tables in the first row, you could say that Genres is on the "one" side and Programs is on the "many" side.

I'm not certain whether showing which table is on the "one" side and which is on the "many" side is helpful or confusing. Some authors distinguish one-to-many relationships from many-to-one relationships, but I don't know how to communicate a difference in a way that will be profitable to you. Instead, I've explained that both tables are in a one-to-many relationship and then indicated which is on the "one" side and which is on the "many" side.

In the following tables, however, it was useful to show the various relationships as 1:M (one-to-many) and M:1 (many to one). Many-to-many relationships are shown as M:M.