Understanding the Three Types of Table Relationships

Part of the science (or art, some may say) of joining tables involves under- standing how the database designer intends for the tables to relate, also known as the database's relational model. There are three types of table relationships: one to one, one to many, and many to many.

One-to-One Relationship

In our JOIN example in Listing 7-4, there are no duplicate id values in either table: only one row in the district 2020 table exists with an id of 1, and only one row in the district 2035 table has an id of 1. That means any given id in either table will find no more than one match in the other table. In database parlance, this is called a one-to-one relationship.

Consider another example: joining two tables with state-by-state census data. One table might contain household income data and the other data is about educational attainment. Both tables would have 51 rows (one for each state plus Washington, D.C.), and if we joined them on a key such as state name, state abbreviation, or a standard geography code, we'd have only one match for each key value in each table.

One-to-Many Relationship

In a one-to-many relationship, a key value in one table will have multiple matching values in another table's joined column. Consider a database that tracks automobiles. One table would hold data on manufacturers, with one row each for Ford, Honda, Tesla, and so on. A second table with model names, such as Mustang, Civic, Model 3, and Accord, would have several rows matching each row in the manufacturers' table.

Many-to-Many Relationship

A many-to-many relationship exists when multiple items in one table can relate to multiple items in another table, and vice versa. For example, in a baseball league, each player can be assigned to multiple positions, and each position can be played by multiple players. Because of this complexity, many-to-many relationships usually feature a third, intermediate table in between the two. In the case of the baseball league, a database might have a players table, a positions table, and a third called players positions that has two columns that support the many-to-many relationship: the id from the players table and the id from the positions table.

Understanding these relationships is essential because it helps us discern whether the results of queries accurately reflect the structure of the database.