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