Can be of three types: one-to-many, one- to-one, or many-to-many.
One-to-many (1:M). The most common type of relationship cardinality is a 1:M relationship. Consider the relationship between the EMPLOYEE and DEPARTMENT tables shown in Figure 1. The common column is DEPARTMENT_ID (which is the primary key in the DEPARTMENT table and the foreign key in the EMPLOYEE table). One individual DEPARTMENT_ID can relate to many rows in the EMPLOYEE table. This represents the business rule that one department can relate to one or many employees (or even no employees) and that an employee is associated with only one department (or, in some cases, no department). This business rule can be restated as follows: Each employee in a department may be in one and only one department, and that department must exist in the department table.
One-to-one (1:1). A 1:1 relationship between the DEPARTMENT table and the MANAGER table is depicted in Figure 2. For every row in the DEPARTMENT table, only one matching row exists in the MANAGER table. Expressing this as a business rule: Every department has at least one and at most one manager, and, conversely, each manager is assigned to at least one and at most one department.
One-to-one data relationships can exist, but they are not typically implemented as two tables (though they may be modeled that way). Instead, the data is combined into one table for the sake of simplicity.
Many-to-many (M:M). Consider the EMPLOYEE and PROJECT tables in Figure 3. The business rule is as follows: One employee can be assigned to multiple projects, and one project can be supported by multiple employees. Therefore, it is necessary to create an M:M relationship to link these two tables.
To support the relational database model, an M:M relationship must be resolved into 1:M relationships. Figure 4 illustrates this resolution with the creation of an associative table (also sometimes called an intermediate or intersection table) named EMPLOYEE_PROJECT.
In this example, the associative table’s primary key—a composite of its Employee_ID and Project_ID columns—is foreign-key-linked to the tables for which it is resolving an M:M relationship. It reflects that one employee can be assigned to multiple projects—and, in this example, that one employee can be assigned multiple and different responsibilities for each project to which that person is assigned. Note that the employee with an EMPLOYEE_ID value of 1234 is assigned to two projects but that his responsibilities are different for each project.
One-to-many (1:M). The most common type of relationship cardinality is a 1:M relationship. Consider the relationship between the EMPLOYEE and DEPARTMENT tables shown in Figure 1. The common column is DEPARTMENT_ID (which is the primary key in the DEPARTMENT table and the foreign key in the EMPLOYEE table). One individual DEPARTMENT_ID can relate to many rows in the EMPLOYEE table. This represents the business rule that one department can relate to one or many employees (or even no employees) and that an employee is associated with only one department (or, in some cases, no department). This business rule can be restated as follows: Each employee in a department may be in one and only one department, and that department must exist in the department table.
One-to-one (1:1). A 1:1 relationship between the DEPARTMENT table and the MANAGER table is depicted in Figure 2. For every row in the DEPARTMENT table, only one matching row exists in the MANAGER table. Expressing this as a business rule: Every department has at least one and at most one manager, and, conversely, each manager is assigned to at least one and at most one department.
One-to-one data relationships can exist, but they are not typically implemented as two tables (though they may be modeled that way). Instead, the data is combined into one table for the sake of simplicity.
Many-to-many (M:M). Consider the EMPLOYEE and PROJECT tables in Figure 3. The business rule is as follows: One employee can be assigned to multiple projects, and one project can be supported by multiple employees. Therefore, it is necessary to create an M:M relationship to link these two tables.
To support the relational database model, an M:M relationship must be resolved into 1:M relationships. Figure 4 illustrates this resolution with the creation of an associative table (also sometimes called an intermediate or intersection table) named EMPLOYEE_PROJECT.
In this example, the associative table’s primary key—a composite of its Employee_ID and Project_ID columns—is foreign-key-linked to the tables for which it is resolving an M:M relationship. It reflects that one employee can be assigned to multiple projects—and, in this example, that one employee can be assigned multiple and different responsibilities for each project to which that person is assigned. Note that the employee with an EMPLOYEE_ID value of 1234 is assigned to two projects but that his responsibilities are different for each project.
Comments
Post a Comment