Entities for different types of relationships

In a relational database, you can express several types of relationships.

Consider the possible relationships between employees and departments. If 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.

Database designers and data analysts can be more effective when they have a good understanding of the business. If they understand the data, the applications and the business rules, they can succeed in building a sound database design.

When you define relationships, you have a large influence on how smoothly your business runs. If you define relationships poorly, your database and associated applications are likely to have many problems, some of which might not manifest themselves for years.

The following are the different types of relationships in a relational database:

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

When you are doing logical database design, one-to-one relationships are bidirectional relationships, which means that they are single-valued in both directions. For example, an employee has a single resume; each resume belongs to only one person. The previous 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 and many-to-one relationships

A one-to-many relationship occurs when one entity has a multivalued relationship with another entity. In the following figure, you see that a one-to-many relationship exists between the 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 many-to-one 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.

Try to answer the following questions using the information in the Db2 sample tables:

  • 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.

Business rules for relationships

Whether a given relationship is one-to-one, one-to-many, many-to-one, or many-to-many, your relationships need to make good business sense. Therefore, database designers and data analysts can be more effective when they have a good understanding of the business. If they understand the data, the applications, and the business rules, they can succeed in building a sound database design.

When you define relationships, you have a big influence on how smoothly your business runs. If you don't do a good job at this task, your database and associated applications are likely to have many problems, some of which may not manifest themselves for years.