---[REF]---"style":"heading"---[REF]--- ---[SIDEBAR]--- "type":"objectives", "title":"Table Relationships and Cardinality", "text":"After completing this lesson, the successful student will be able to:

1. Discuss how database tables are related.
2. Describe relationship cardinality.
3. Explain how cardinality impacts the use of foreign keys.
4. Complete a business model of a database with details about relationships.

" ---[SIDEBAR]--- ---[REF]---"style":"link","pageId":"lesson-designing-tables"---[REF]--- 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 to be able to write effective queries that include multiple tables. ## Review: The Business Model of a Database Recall that tables are designed to store data about a different themes. All of the data about a particular theme is stored on a single table. For example, the tables on ---[museum-business-model-keys]--- reflect the simple business model of the museum data that we developed in ---[REF]---"style":"link","pageId":"lesson-designing-tables"---[REF]---. 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]--- "id":"museum-business-model-keys", "title":"Business Model of Museum Database", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLnXCAz5bh8hDTbNAD4wXO3JOXRb4VXXMTBDj7VmC4Hfj87NnRlcqQRy0XgEMLeZc4FEp2zxHyvs58eQZBSqLI_kjqdQb0lWGpgtgUBj3fjq8tkAtTex-wQ9sGR7LiHhffmb-bl4zuNQZY2DBzZhyphenhyphenLXtElKY26dp4xIiptWStQO9DZ1dVqfOQ5rP_APh85/s2394/MuseumBusinessModelKeys.png", "altText": "The Business Model Diagram of the Museum Database.", "imgHandling": "scale" ---[FIGURE]--- Organizing data into separate tables based on theme has some important advantages. These are described in detail in ---[REF]---"style":"link","pageId":"lesson-normalization"---[REF]---. However, important connections exist between data from different themes. For example, artists create works of art and works of art are exhibited in museums. ## 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. ---[SIDEBAR]--- "type":"term", "title":"Relationship", "text":"The naturally occurring associations among the data in different tables." ---[SIDEBAR]--- Consider the two relationships modeled in ---[museum-business-model-keys]---. 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 artists that created each work of art. The relationship between the work and the museum tables likewise implies that it is possible to identify which works of art are exhibited in each museum. Additionally, using both relationships it is possible to identify which artist have works of art in each museum. First you would identify the works of art associated with a specific artist. You would 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) that is included on a related table to connect the rows of the tables together. The primary key of the artist table is the artist_id. The artist_id can also be added to the work table to identify the artist that created each work of art. Additionally, the primary key of the museum table, museum_id, could be added to the work table to identity the museums that exhibit each work of art. You might be tempted to use the primary key from the work table, work_id, on the artist table as a foreign key. This would probably not work well, though, because an artist can create more than one work of art. This would mean that more than one work_id would be included on the artist table for each work of art an artist creates. This would violate proper table design (see ---[REF]---"style":"link","pageId":"lesson-designing-tables"---[REF]---). To determine how best to designate foreign keys to link tables, you need to understand cardinality. ## Cardinality Cardinality refers to the nature of the relationship between two tables. It is always defined in terms of how the individuals rows on the tables are related to each other. Cardinality can be described in terms of minimum cardinality and maximum cardinality. Minimum cardinality describes whether a relationship is optional or mandatory. ---[SIDEBAR]--- "type":"term", "title":"Cardinality", "text":"The nature of the relationship between two tables." ---[SIDEBAR]--- ---[SIDEBAR]--- "type":"term", "title":"Minimum Cardinality", "text":"Describes whether a relationship is optional or mandatory." ---[SIDEBAR]--- Consider the relationship between the artist and work tables. Minimum cardinality would establish two things: 1. If an artist can be included in the database exist without a creating works of art. 2. If a work of art can be included in the database without an artist. Answering these questions will inform how the artist and work tables will be built. Maximum cardinality identifies how many 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. ---[SIDEBAR]--- "type":"term", "title":"Maximum Cardinality", "text":"Identifies how many how many rows from one table can be associated with rows from the related table." ---[SIDEBAR]--- It is only possible for a given work of art to be exhibited in one museum at a time. Therefore, we would say that the maximum cardinality moving from work to museum is 1. It is possible for a given museum to exhibit more than one work of art at a time, so the maximum cardinality moving from museum to work is many. Note that any number greater than 1 is classified as many. Taken together, the maximum cardinality is said to be 1 to many (or 1:M). Just as minimum cardinality can informs the design of database tables, maximum cardinality determines how to use foreign keys to link the rows of different tables together. Although minimum cardinality provides helpful information, the rest of this lesson will focus on how maximum cardinality helps you to understand the nature of relationships between tables. ### Business Rules Often there a logical constraints placed on a relationship based on how the organization conducts business. These constraints are called business rules. For example, a database may be used to keep track of books and patrons for a library. Since patrons check out books, there is likely a database relationship between a patron table (used to store data about library patrons) and a book table (which stores data about the library’s books). ---[SIDEBAR]--- "type":"term", "title":"Business Rules", "text":"Constraints placed on a relationship based on how the organization conducts business." ---[SIDEBAR]--- A patron could check out any number of books at a time, but the library will probably have a policy limiting the number of books. This would be a business rule. Relationships can be designed to enforce business rules. Likewise, a business rule might determine the cardinality of table relationships. ## Maximum Cardinality Types There are logically three ways to describe relationships between tables in terms of maximum cardinality. These are one to one (1:1), one to many (1:M), and many to many (M:M). Each type of relationship has a different way to use foreign keys. Each is discussed below. ### One to one A one to one relationship occurs when instances on two tables are associated with only one instance on the other table in both directions. Suppose we have a database that contains a table with information about countries and a table that contains information about capital cities. (See ---[one-to-one]---). Each country has only one capital city and each capital city is the capital of only one country. This relationships is one to one (1:1). ---[FIGURE]--- "id":"one-to-one", "title":"Countries and Capitals", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7JRbP-Ds5ln0YXmQyj-jzyjaojocsdNUlb8FpO04HwPWJdQrofWokxgPsE7fHb8SCZl3BpDBqvGkD7VSGddP1TF2-wGfT-Ce0jCTxp6XggWdceklR6IjYNLQw29-YSJElEwspNuMvdJqGt4acTRP7ffVUs3kSdPLLhFLLx5rePULhpe3BLUbe4jAlu-Zc/w640-h188/OneToOneRelationship.png", "altText": "Countries and capitals as an example of a one to one relationship.", "imgHandling": "scale" ---[FIGURE]--- Notice how the ‘1’ on both ends of the line connecting these tables. The notes that the relationship between the tables 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 primary key is included on the “other” table as a foreign key. However, only one foreign key (between the two tables) is needed to link the tables together. ---[one-to-one-foreign-key]--- 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]--- "id":"one-to-one-foreign-key", "title":"Linking Countries and Capitals Using a Foreign Key", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5W2OZPk3KZsaLIkjRDPu3Pyjq_POj3-0nfX5koQBSY4AsyqHnHERpSbR65nrfb3c3OPWR8yEZH3_HM9YIPQXI5vxwE_POHdEJlqR86puZUmj4hPRLmz5Zk4ZTQUZwQ_G1ogfy0PhY45T6xdOrPQKD9quarBY16_GfocMeWBlbN9Wev0kvoqNIrW5Hwk91/w640-h108/OneToOneForeignKey.png", "altText": "How to Link Countries and Capitals Using a Foreign Key.", "imgHandling": "scale" ---[FIGURE]--- ---[SIDEBAR]--- "type":"note", "title":"One to One (1:1) Relationships", "text":"With a one to many relationship, select a primary key from either table and add it as a foreign key on the other table. Only one table needs to have a foreign key." ---[SIDEBAR]--- ### 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 one is related to only one row of the related table. Suppose 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 ---[one-to-many]---). ---[FIGURE]--- "id":"one-to-many", "title":"Countries and Cities", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJLvdj2wA5RSzHlQj5AiBZttmO2qFXsBZDH2oe4iLlQ_9Qn3esHe6z5907YsDl8Gm7Y-2-QXKtcCa7p8Xai22x2oiVh1pXjljFZVHaX3jHbgis1L0plHR5fpkiRMhwwikj7co0SqRjCGxb_voM9f6AF_qOhtg9YQYZ1wwXFnyEaM0VETA43X9cUdpOW0pr/w640-h186/OneToManyRelationship.png", "altText": "Countries and cities as an example of a one to many relationship.", "imgHandling": "scale" ---[FIGURE]--- Notice the ‘1’ on near the “country” end of the relationship line and the M near the “City” end of the line, noting that the relationship is one to many (1:M). This means that a city is associated with only one country, but a country can be associated with more than one city. Also notice the country_id is included as a foreign key on the city table. Recall that in a one to one relationship, it doesn’t matter if which table included the foreign key. However, in a one to many relationship, 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. ---[one-to-many-foreign-key]--- 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 the country_id repeats on the city table. This makes sense because you are expecting that 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 on the country table is acting as the primary key, but it is a foreign key on the city table. ---[FIGURE]--- "id":"one-to-many-foreign-key", "title":"Countries and Cities", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiew9oAS6mpp1m2zF8RlsaaqUitcpDSsVQz5fHwSsE0XZy-riE1Ijlb1DprwO3W2y93QE6uTS-fso1qh1xpz_LVWK5FdfHW5Mg6CE85y1CPauVz8hkyRuOXsIIBIEKfmPCcRDeSMcBC1bkSyMyv67j6UMuW19DD0N2uXbcxDGYE0rKuP2K-JTnl7H_uO14Z/w640-h108/OneToManyForeignKey.png", "altText": "Countries and cities as an example of a one to many relationship.", "imgHandling": "scale" ---[FIGURE]--- You might be curious to know why you can’t include the city_id on the country table as a foreign key. The following figures demonstrate why this is the case. The first attempt (---[one-to-many-foreign-key-attempt1]---) places the city_id for each city 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. In this case, the city_id for the USA has two values so it is not in it’s simplest form. ---[FIGURE]--- "id":"one-to-many-foreign-key-attempt1", "title":"City_id as Foreign Key - Attempt 1", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZhiI8ffxl6EADL4KIx7QpLzG1pQMfYdJv3S4ruJUen6WB_BAUvP7e9BHOk5YY-Dkc8BemBwC5ZYxAKqTyOwvUnufqmZawmoFtCPYNwAoIsolQscZX2vQW5LgJP9H1oUduGIIghbS8CBbHmbmvCid_X0Tq-uhRcEL46JTPA6uzAw0yMcCBi83kCAkgpDKm/s16000/OneToManyForeignKeyAttempt1.png", "altText": "Example of why city_id can't be a foreign key.", "imgHandling": "scale" ---[FIGURE]--- A second attempt (---[one-to-many-foreign-key-attempt2]---) adds multiple columns for the city_id to accommodate multiple cities in the same country. Again, this may seem like an attractive option. However, this approach would make it very difficult to write a query that effectively connects countries to the cities. Additionally, it would be difficult to know just 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 in the database. Finally, notice how there is a null value for city_id2 for France. As a database designer, you want to avoid null values when you can. All three of these issues make the approach in ---[one-to-many-foreign-key]--- must less desirable than placing the country_id as a foreign key on the city table. ---[FIGURE]--- "id":"one-to-many-foreign-key-attempt2", "title":"City_id as Foreign Key - Attempt 2", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvEAdJZFBhofek8NX9EHiIKqFEEbdtUh9Wj_Hw5FJkIapdbzqoOeGtYXkzOVTeLX6SYqT18ne0_Ni4zCctKKE8zpVgJQkk0ALub8zAtMuzdpN_wOPEeTAsfNl8j7qpoWNa4febNp2mxk34vMpHWIHl7krZrRVVxDgNA3X9HSqZzCfzOhpmrFe0tlyH4t9r/w640-h107/OneToManyForeignKeyAttempt2.png", "altText": "Example of why city_id can't be a foreign key.", "imgHandling": "scale" ---[FIGURE]--- A final attempt to use city_id on the country table as a foreign key is presented in ---[one-to-many-foreign-key-attempt3]---. Here, a new row for the USA is added. The question then becomes what to use at the primary key for the second USA row. You can’t use the original primary key of 1, because each value of the primary key column must be different. If you assign a primary key of 3 to the second USA row, you are technically adding a new country to the table (which just happens to have the same name at the first USA entry). Either way, there is no way to appropriately assign a primary key value to the new USA row. This approach doesn’t work, either. ---[FIGURE]--- "id":"one-to-many-foreign-key-attempt3", "title":"City_id as Foreign Key - Attempt 3", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6EmcdCwzEaqOpNogkwvv0afLnj0PDjgc2VPrDWgBZzgzJGTGHWBfYMlC4rvo8ROMAYoK7lP8hhKP40vgQwtVk3Ohd0tIK_4ZAjy3fLGny-bm-7Cn0HVQGEy0_L5MVKGoyImGhf1M5P1rZnZe75tRol00kEqHHYIzFzGJ6GsUgj1H28HfaLizzEuRLxIwA/w640-h118/OneToManyForeignKeyAttempt3.png", "altText": "Example of why city_id can't be a foreign key.", "imgHandling": "scale" ---[FIGURE]--- The only way to create a foreign key to link the country and city tables is to add the country_id to the city table. This places the primary key from the table on the “one” side of the relationship as a foreign key on the table on the “many” side of the relationship. This will always be the case for one to many (1:M) relationships. ---[SIDEBAR]--- "type":"note", "title":"One to Many (1:M) Relationships", "text":"With a one to many relationship, take the primary key from the table on the 'one' side of the relationship and add it as a foreign key column in the table on the 'many side of the relationship'." ---[SIDEBAR]--- ### Many to Many A many to many (M:M) relationship happens when, moving in both directions, a single row on one table in the relationship could correspond with more than one row on the other table. Suppose the database includes a country table and a table that stores data about citizens. An individual country could have more than one citizen and an individual person can have dual citizenship, or be a citizen of more than one country. ---[many-to-many-relationship]--- show this relationship. ---[FIGURE]--- "id":"many-to-many-relationship", "title":"Countries and Citizens", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNouyS-81v3D4-XkH61UirShyphenhyphenqkmd_M5wUAJnQhDrlejyVnkGCaX4Bwa7FVq0H98-nLOz17eEK1YYXTP6pWc4K06cSqTjry6SXYNX7M1DPxjfqL6HYRvr9maur8DrgMJOKcDwxJKYdM9NejQd0sNl6qAgId_GFZSMBZ92CCDGv-vJTeJVQj31hFHKCwQ7u/w640-h186/ManyToManyRelationship.png", "altText": "Countries and citizens: an example of an unresolved many to many relationship.", "imgHandling": "scale" ---[FIGURE]--- 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 issues will apply to a many to many (M:M) relationship. This presents an interesting quandary since there both tables are on the “many” side of this 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 or a relationship table. The name of this table is often a combination of the names of the original tables. In this case the table might be named ‘Country_Citizen’. The purpose of this table is to create a link between two tables with a many to many (M:M) relationship. The relationship between these two original tables and the new linking table will always be one to many (1:M). Therefore, the primary keys from the original tables will be included on the linking table as a foreign keys. ---[many-to-many-relationship-resolved]--- shows how this relationship table would work. ---[FIGURE]--- "id":"many-to-many-relationship-resolved", "title":"Countries and Citizens Relationship Resolved", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUSN9FnQ9TytIz8IBkHuIS5VjGTU2u_W_oTR3gFcpLiWWR0Q9FRl2VFZ7xci88HpMbAcoZR6cB_qsryqWqShJMYpx4BRbx6aTOZbUFbo516bbHL00czmFNvWNyDe9-vXhtjABnQl8-tXmcg5_pxquE6HYwwcKu_1rPqQm4d8e5abURpUP-6QZn0cq10_6d/w640-h128/ManyToManyRelationshipResolved.png", "altText": "Countries and citizens: an example of a resolved many to many relationship.", "imgHandling": "scale" ---[FIGURE]--- ---[many-to-many-relationship-resolved-foreign-keys]--- shows how countries and citizens would be linked using the Country_Citizen table. The first row of Country_Citizen table links citizen 2 (June Day) with country 1 (USA). Rows 2 and 3 of the table designate Frank Bass (citizen 3) as a citizen of the USA (country 1) and France (country 2). Finally the last row links citizen 1 (Liv Jones) with country 2 (France). ---[FIGURE]--- "id":"many-to-many-relationship-resolved-foreign-keys", "title":"Countries and Citizens Linked with Foreign Keys", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj48QwKKzHBo-FRU7S_aHaCcQa7JFC3CLRodrnraQD3MFpbcwN2wC76bqz9mMpCXUn1nKzxD5imbxk3zwB8CyF4KNmfl5t7jm73ZG7tpe5X4hJi0BescMwfBpHAKIW9fCWb3HAjPSRIlIpClh3z_R-jysgimz6rWmEov6GO1f60LwpVS8Ny_od4Y4JlFuRd/w640-h128/ManyToManyRelationshipResolvedFK.png", "altText": "Countries and citizens: an example of a linking rows in a many to many relationship.", "imgHandling": "scale" ---[FIGURE]--- ---[SIDEBAR]--- "type":"note", "title":"Many to Many (M:M) Relationships", "text":"With a one to many relationship, create a linking table. The original tables each have a one to many (1:M) relationship with the linking table." ---[SIDEBAR]--- ## Relationships in the Museum Business Model With an understanding of cardinality and the impact of cardinality on foreign keys, you can complete the business model for the museum database. ---[business-model-museum-db]--- shows the business current business model. ---[FIGURE]--- "id":"business-model-museum-db", "title":"Business Model of Museum Database", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0PSKX0Lp0lRXwgEeAIfxUUTRKRra4kHf6w40RlYuOjZyJ5DJA0tmUa_fZHHH5WKcN94WT6byiNE5NlP9O3FU2YqQm6DTvlMIRDu2pk2SJLBQN6DBtKVUZqxqGvx-59BB9M4TXymMtqURM_6OBy9o6pi3Jit258wwOPIrW1MRwF4Bn0js33YaJak70HeFF/s2692/BusinessModelMuseumDB.png", "altText": "A business model of the museum database.", "imgHandling": "scale" ---[FIGURE]--- 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 one creating artist. This means that 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 an work is 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). The museum_id will be included as a foreign key column of the work table. ---[business-model-museum-db-complete]--- shows the completed business model for the museum database. Note that the museum table has more than one foreign key. This is not only allowable, but is common. Also note that both relationships have a cardinality of one to many (1:M). This is also common. Most relationships have one to many (1:M) cardinality. ---[SIDEBAR]--- "type":"tip", "title":"Tables can have multiple foreign key columns", "text":"Although it may seem counterintuitive, a table can have more than one foreign key column. In fact this is not uncommon." ---[SIDEBAR]--- ---[FIGURE]--- "id":"business-model-museum-db-complete", "title":"Complete Business Model of Museum Database", "url":"https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhR0v1TMLVUXIh5msEZoZIKgBvGLxfNTp6Hnyy1pt4xK5QDgyLxx4sYh83jG4b5Gy9qfz3NMerhaFUyOsmFmtR9efcCaKijpBBOALHIsRmmT1upjqTa1ZusdtuEXgo46E6BS40GJPMaRwTrh14dWE3GoQLmbdzTKwEa8IMLM0TUlbIfh6SXvmzYWCVdp2jd/w640-h262/BusinessModelMuseumDBComplete.png", "altText": "A complete business model of the museum database with relationships modeled.", "imgHandling": "scale" ---[FIGURE]--- ### Summary This lesson described relationships between tables. It also discussed cardinality and the implications of cardinality 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.