Entities for different types of relationships

In a relational database, separate entities must be defined for different types of relationships.

In a relational database, you can express several types of relationships. Consider the possible relationships between employees and departments. A given employee can work in only one department; this relationship is one-to-one for employees. One department usually has many employees; this relationship is one-to-many for departments. 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 need to define separate entities for different types of relationships. When modeling relationships, you can use diagram conventions to depict relationships by using different styles of lines to connect the entities.

One-to-one relationships
A one-to-one relationship in database design is bidirectional relationship, which means it is single-valued in both directions.

For example, an employee has a single resume; each resume belongs to only one person. The following figure illustrates that a one-to-one relationship exists between the two entities. In this case, the relationship reflects the rules that an employee can have only one resume and that a resume can belong to only one employee.

Figure 1. Assigning one-to-one facts to an entity
Begin figure description. This figure shows a one-to-one relationship between two entities. End figure description.
One-to-many relationships
A one-to-many relationship occurs in database design when one entity has a multivalued relationship with another entity.

In the following figure, you see that a one-to-many relationship exists between two entities—employee and department. This figure reinforces the business rules that a department can have many employees, but that each individual employee can work for only one department.

Figure 2. Assigning many-to-one facts to an entity
Begin figure description. This figure shows a one-to-many relationship between two entities. End figure description.
Many-to-many relationships
A many-to-many relationship is a relationship that is multivalued in both directions.

The following figure illustrates this kind of relationship. An employee can work on more than one project, and a project can have more than one employee assigned.

Figure 3. Assigning many-to-many facts to an entity
Begin figure description. This figure shows a many-to-many relationship between entities. End figure description.

If you look at this information's example tables, you can find answers for the following questions:

  • What does Wing Lee work on?
  • Who works on project number OP2012?

Both questions yield multiple answers. Wing Lee works on project numbers OP2011 and OP2012. The employees who work on project number OP2012 are Ramlal Mehta and Wing Lee.