In a database, you can express several types of relationships. Consider the possible relationships between employees and departments. An employee can work in only one department; this relationship is single-valued for employees. On the other hand, one department can have many employees; the relationship is multi-valued for departments. The relationship between employees (single-valued) and departments (multi-valued) is a one-to-many relationship. Relationships can be one-to-many, many-to-one, one-to-one, or many-to-many.
The type of a given relationship can vary, depending on the specific environment. If employees of a company belong to several departments, the relationship between employees and departments is many-to-many.
You will want to define separate tables for different types of relationships.
The following topics are discussed within this section:
To define tables for each one-to-many and many-to-one relationship:
In the following example, the "many" side of the first and second
relationships is "employees" so we define an employee table,
EMPLOYEE.
Table 3. Many-to-One Relationships
| Entity | Relationship | Entity |
|---|---|---|
| Employees | are assigned to | departments |
| Employees | work at | jobs |
| Departments | report to | (administrative) departments |
In the third relationship, "departments" is the "many" side, so we define a department table, DEPARTMENT.
The following tables illustrate how these examples are represented:
Figure 12. Assigning Many-to-One Facts to Tables
The EMPLOYEE table:
The DEPARTMENT table:
|
A relationship that is multi-valued in both directions is a many-to-many relationship. An employee can work on more than one project, and a project can have more than one employee. The questions "What does Dolores Quintana work on?" and "Who works on project IF1000?" both yield multiple answers. A many-to-many relationship can be expressed in a table with a column for each entity ("employees" and "projects"), as shown in the following example.
The following table illustrates how a many-to-many relationship (an employee can work on many projects and a project can have many employees working on it) can be represented:
Figure 13. Assigning Many-to-Many Facts to Tables
The employee activity (EMP_ACT) table:
|
One-to-one relationships are single-valued in both directions. A manager manages one department; a department has only one manager. The questions, "Who is the manager of Department C01?" and "What department does Sally Kwan manage?" both have single answers. The relationship can be assigned to either the department table or the employee table. Because all departments have managers, but not all employees are managers, it is most logical to add the manager to the department table as shown in the following example.
The following tables illustrates how a one-to-one relationship can be represented:
Figure 14. Assigning One-to-One Facts to a Table
The DEPARTMENT table:
|