14 Table Relationships and Cardinality

The lesson on designing database tables described how database tables are designed. In this lesson, you will learn how to design the relationships among database tables. Understanding relationships between tables is important for writing effective queries that combine data from multiple tables.

14.1 Review: The Business Model of a Database

Recall that tables are designed to store data about different themes — all of the data about a particular theme is stored on a single table. For example, the tables in Figure 1 reflect the simple business model of the museum data developed in the lesson on designing database tables. All of the information about artists is stored on the artist table; notice that the artist table does not contain information about works of art or museums. Likewise, the work and museum tables contain data exclusive to works of art and museums, respectively.

Figure 1: Business Model of Museum Database

Organizing data into separate tables based on theme has some important advantages, described in detail in a later lesson on database normalization. However, important connections exist between data from different themes — for example, artists create works of art, and works of art are exhibited in museums.

14.2 Relationships Between Tables

The next step in completing a business model for a database is to model relationships. Relationships are the naturally occurring associations among the data in different tables. Relationships are most easily conceptualized between two tables at a time.

Consider the two relationships modeled in Figure 1. There is a relationship between the artist and work tables, and a relationship between the work and museum tables. The relationship between the artist and work tables implies that it is possible to identify the specific artist who created each work of art. The relationship between the work and museum tables likewise implies that it is possible to identify which works of art are exhibited in each museum. Using both relationships together, it is possible to identify which artists have works of art in each museum: first you would identify the works of art associated with a specific artist, then identify the museums that exhibit those works of art.

It is possible to associate specific rows from one table with rows on a related table by using foreign keys. A foreign key is a column on one table (generally the primary key of that table) that is included on a related table to connect the rows of the two tables together. The primary key of the artist table is artist_id. That same artist_id can be added to the work table to identify the artist who created each work of art. Similarly, the primary key of the museum table, museum_id, could be added to the work table to identify the museum that exhibits each work of art.

You might be tempted to instead use the primary key from the work table, work_id, on the artist table as a foreign key. This would not work well, though, because an artist can create more than one work of art, which would mean more than one work_id would need to be stored for a single artist. This would violate proper table design (see the lesson on designing database tables). To determine how best to designate foreign keys to link tables, you need to understand cardinality.

14.3 Cardinality

Cardinality refers to the nature of the relationship between two tables. It is always defined in terms of how the individual rows on the tables relate to each other, and it can be described in terms of minimum cardinality and maximum cardinality. Minimum cardinality describes whether a relationship is optional or mandatory.

Consider the relationship between the artist and work tables. Minimum cardinality would establish two things:

1. Whether an artist can exist in the database without having created any works of art.
2. Whether a work of art can exist in the database without an associated artist.

Answering these questions informs how the artist and work tables will be built. This lesson returns to these two questions later, in the Minimum Cardinality Types section, after maximum cardinality has been explored in more depth.

Maximum cardinality identifies how many rows from one table can be associated with rows from the related table. This is best understood by looking at an example. Consider the relationship between the museum and work tables. Maximum cardinality determines: (1) how many museums can exhibit a given work of art, and (2) how many works of art can be exhibited in a given museum.

A given work of art can only be exhibited in one museum at a time, so the maximum cardinality moving from work to museum is 1. A given museum, on the other hand, can exhibit more than one work of art, so the maximum cardinality moving from museum to work is many. (Note that any number greater than 1 is classified as many.) Taken together, this relationship's maximum cardinality is said to be one to many (1:M).

Just as minimum cardinality informs the design of database tables, maximum cardinality determines how foreign keys are used to link the rows of different tables together. Maximum cardinality is discussed first, since it has the biggest impact on how tables are linked together; minimum cardinality is revisited afterward, in the Minimum Cardinality Types section.

14.3.1 Business Rules

Often there are logical constraints placed on a relationship based on how an organization conducts business. These constraints are called business rules. For example, a database used to track books and patrons for a library would likely have a relationship between a patron table (storing data about library patrons) and a book table (storing data about the library's books).

A patron could check out any number of books at a time, but the library will probably have a policy limiting how many — say, no more than five books at once. This is a business rule, and it places a cardinality constraint on the relationship between the patron and book tables.

A cardinality constraint does not change how a relationship is classified. The patron-book relationship is still one to many — a book can only be checked out by one patron at a time, but a patron can check out more than one book. The cardinality constraint simply adds a business-specific limit on top of that classification: instead of "many," a patron is limited to at most five books at once. Enforcing a cardinality constraint like this one usually requires application logic, since a foreign key alone cannot guarantee it.

14.4 Maximum Cardinality Types

There are three ways to describe relationships between tables in terms of maximum cardinality: one to one (1:1), one to many (1:M), and many to many (M:M). Each type of relationship calls for a different way of using foreign keys, discussed below.

14.4.1 One to One

A one-to-one relationship occurs when, in both directions, an instance on one table is associated with only one instance on the other table. Suppose a database contains a table with information about countries and a table with information about capital cities (see Figure 2). Each country has only one capital city, and each capital city is the capital of only one country — this relationship is one to one (1:1).

Figure 2: Countries and Capitals

Notice the "1" on both ends of the line connecting these tables, denoting that the relationship between them is one to one (1:1). Also notice that the primary key of the country table is included as a column that acts as the foreign key for the capital table. With a one-to-one relationship, it doesn't matter which table's primary key is included on the other table as a foreign key — only one foreign key (between the two tables) is needed to link them together. Figure 3 shows how primary and foreign key values are used to link the rows from the country table with the rows on the capital city table.

Figure 3: Linking Countries and Capitals Using a Foreign Key

14.4.2 One to Many

A one-to-many relationship occurs when, moving in one direction, a row on one table is associated with more than one row on the related table, but moving in the other direction, a row is associated with only one row of the related table. Suppose that instead of a country and capital table, a database includes a table for countries and a table for cities. Each city is located in only one country, but a country can have more than one city (see Figure 4).

Figure 4: Countries and Cities

Notice the "1" near the country end of the relationship line and the "M" near the city end, indicating that the relationship is one to many (1:M) — a city is associated with only one country, but a country can be associated with more than one city. Also notice that country_id is included as a foreign key on the city table. Recall that in a one-to-one relationship, it doesn't matter which table includes the foreign key. In a one-to-many relationship, however, the primary key from the table on the "1" side of the relationship must be included as the foreign key on the "many" side of the relationship.

Figure 5 shows how primary and foreign key values are used to link the rows from the country table with the rows on the city table. You may be surprised to see country_id repeated on the city table, but this makes sense, since a country can have more than one city. Unlike primary keys, which must always be unique, foreign key values can repeat. The country_id column acts as the primary key on the country table, but it is a foreign key on the city table.

Figure 5: Countries and Cities Linked with a Foreign Key

You might be curious why you can't instead include city_id on the country table as a foreign key. The next few figures demonstrate why this doesn't work. The first attempt (Figure 6) places the city_id values for each country's cities into a single column on the country table. While this may look like an attractive solution, it violates an important principle of table design: atomicity. Atomicity means that the values in a column are decomposed to their simplest form, and in this case the city_id value for the USA row would contain two values, so it is not in its simplest form.

Figure 6: City_id as Foreign Key — Attempt 1

A second attempt (Figure 7) adds multiple city_id columns to accommodate multiple cities in the same country. This may also seem attractive, but it would make it very difficult to write a query that effectively connects countries to cities. It would also be difficult to know how many city_id columns to include on the country table to account for all of the current and future cities you want to store, and notice the null value for city_id2 on the France row — as a database designer, you want to avoid null values when you can. All three of these issues make this approach much less desirable than placing country_id as a foreign key on the city table.

Figure 7: City_id as Foreign Key — Attempt 2

A final attempt to use city_id on the country table as a foreign key is shown in Figure 8. Here, a new row for the USA is added, which raises the question of what to use as the primary key for that second USA row. You can't reuse the original primary key value of 1, because every value in a primary key column must be unique. If you instead assign a primary key of 3 to the new USA row, you are technically adding a new country to the table — one that just happens to have the same name as the first USA entry. Either way, there is no way to appropriately assign a primary key value to the new row, so this approach doesn't work either.

Figure 8: City_id as Foreign Key — Attempt 3

The only way to create a foreign key that links the country and city tables is to add country_id to the city table — placing the primary key from the table on the "one" side of the relationship as a foreign key on the table on the "many" side. This will always be the case for one-to-many (1:M) relationships.

14.4.3 Many to Many

A many-to-many (M:M) relationship happens when, moving in both directions, a single row on one table could correspond with more than one row on the other table. Suppose a database includes a country table and a table that stores data about citizens. An individual country can have more than one citizen, and an individual person can have dual citizenship — that is, be a citizen of more than one country. Figure 9 shows this relationship.

Figure 9: Countries and Citizens

The last section described why you cannot take the primary key from a table on the "many" side of a relationship and include it on the other table as a foreign key. Unfortunately, the same issue applies to a many-to-many (M:M) relationship — and here it presents an interesting quandary, since both tables are on the "many" side of the relationship.

To solve this dilemma, you add a third table to the database, between the country and citizen tables. This new table is called a linking table or a relationship table, and its name is often a combination of the names of the original two tables — in this case, it might be named country_citizen.

The purpose of this table is to create a link between two tables that have a many-to-many (M:M) relationship. The relationship between each of the original tables and the new linking table will always be one to many (1:M), so the primary keys from the original tables are included on the linking table as foreign keys. Figure 10 shows how this linking table would work.

Figure 10: Countries and Citizens Relationship Resolved

Figure 11 shows how countries and citizens would be linked using the country_citizen table. The first row of the country_citizen table links citizen 2 (June Day) with country 1 (USA). Rows 2 and 3 designate Frank Bass (citizen 3) as a citizen of both the USA (country 1) and France (country 2). The last row links citizen 1 (Liv Jones) with country 2 (France).

Figure 11: Countries and Citizens Linked with Foreign Keys

14.5 Minimum Cardinality Types

Just as maximum cardinality is described using the labels 1 and M, minimum cardinality is described using the labels 0 and 1. A minimum cardinality of 0 means the relationship is optional in that direction — a row in one table is not required to have a matching row in the related table. A minimum cardinality of 1 means the relationship is mandatory in that direction — a row in one table must have a matching row in the related table.

Like maximum cardinality, minimum cardinality is evaluated separately for each direction of a relationship, and the two directions do not have to match.

14.5.1 Revisiting the Artist and Work Tables

Recall the two questions posed earlier about the artist and work tables:

1. Can an artist exist in the database without having created any works of art?
2. Can a work of art exist in the database without an associated artist?

Suppose the museum keeps records on artists it is considering for a future acquisition, even before it owns any of their work. In that case, the answer to the first question is yes, so the minimum cardinality moving from artist to work is 0 — the relationship is optional in that direction. Now suppose that every work of art in the database must have a known, recorded artist. In that case, the answer to the second question is no, so the minimum cardinality moving from work to artist is 1 — the relationship is mandatory in that direction.

Combined with the maximum cardinality established earlier in this lesson (one to many, moving from artist to work), the full description of this relationship is: an artist is optionally related to many works of art, and a work of art is mandatorily related to exactly one artist.

A relationship diagram can show minimum and maximum cardinality together by placing both values, as a pair, at each end of the relationship line: the minimum cardinality (0 or 1) followed by the maximum cardinality (1 or M).

14.5.2 Minimum Cardinality and Foreign Keys

Minimum cardinality has a direct effect on how a foreign key column is defined. If the minimum cardinality on the "many" side of a relationship is mandatory (1), the foreign key column cannot contain a null value, since every row is required to reference a matching row on the related table. If the minimum cardinality is optional (0), the foreign key column is allowed to contain null values, since a matching row isn't required.

In the museum database, the work table has two foreign key columns: artist_id and museum_id. Given the example above, artist_id would be defined so that it cannot be null, since every work of art must reference an artist. If a work of art could be added to the database before it is assigned to a museum, however, museum_id could be permitted to be null.

14.6 Relationships in the Museum Business Model

With an understanding of cardinality and its impact on foreign keys, you can complete the business model for the museum database. Figure 13 shows the current business model.

Figure 13: Business Model of Museum Database

Consider the relationship between artist and work. The likely cardinality for this relationship is one to many (1:M): an artist can create more than one work of art, but a work of art likely has a single creating artist. This means the primary key from the artist table, artist_id, will be included as a foreign key column on the work table.

The relationship between museum and work will also be one to many (1:M). A museum can exhibit more than one work of art, but a work of art can only be exhibited in one museum at a time, so museum_id will be included as a foreign key column on the work table.

Minimum cardinality should be decided for each relationship as well. As discussed earlier, a work of art must reference a known artist, so the relationship is mandatory moving from work to artist, and the artist_id foreign key column on the work table cannot be null. An artist, however, can be entered into the database before any of their work is recorded, so the relationship is optional moving from artist to work. The museum relationship involves its own decision: if a work of art can be catalogued before it is assigned to a museum, the relationship is optional moving from work to museum, and museum_id is allowed to be null; if every work of art must be assigned to a museum as soon as it is added to the database, the relationship is mandatory instead, and museum_id cannot be null.

Figure 14 shows the completed business model for the museum database. Notice that the work table has more than one foreign key column — this is not only allowable, it is common. Also notice that both relationships have a cardinality of one to many (1:M), which is also common. Most table relationships turn out to be one to many (1:M).

Figure 14: Complete Business Model of Museum Database

14.7 Summary

This lesson described relationships between tables. It also discussed cardinality — both maximum cardinality (one to one, one to many, and many to many) and minimum cardinality (optional and mandatory) — and the implications of each on how foreign keys are used. Finally, it demonstrated how relationships are noted in a business model of a database and how foreign key values link rows from different database tables together.