Creating a Preliminary Data Structure Diagram › Developing a Data Structure Diagram › Representing Entities › Representing Relationships as Entities

Representing Relationships as Entities

Certain relationships defined during the logical design process should be represented as entities in the preliminary data structure diagram. These include:

• Relationships carrying non-key data
• Many-to-many relationships

Another type of relationship, the self-referencing relationship, can become a separate entity in the preliminary data structure diagram or can carry the key to the relationship as a foreign key.

Each of these types of relationships is discussed below.

Relationships carrying non-key data

While most data relationships defined in the logical design contain only foreign keys, some carry both keys and non-key data. Relationships that contain non-key data must be represented as entities as you continue with the physical database design.

For example, because the relationship IS POSITIONED IN carries both keys and non-key data, it must be represented as an entity. Give this new entity an appropriate name.

However, the relationship IS LOCATED should not be represented as an entity because it contains only key information:

   OFFICE CODE (key)
   EMP ID (key)

Many-to-many relationships

In a physical database design, you establish connections between related entities through one-to-many or one-to-one relationships. Each many-to-many relationship defined in the logical design must be converted to two one-to-many relationships. To make this change, you need to represent each many-to-many relationship as an entity, whether it contains non-key data or not. When you derive an entity from a many-to-many relationship, you create two one-to-many relationships, as shown below.

In the Commonweather Corporation, an employee can possess as many as five skills and a specific skill can be held by many employees. This situation establishes a many-to-many relationship between the SKILL and EMPLOYEE entities. Before you implement such a relationship under CA IDMS/DB, you must first create a new entity.

By replacing the many-to-many relationship between EMPLOYEE and SKILL with a new entity, you create two one-to-many relationships:

• A one-to-many relationship is created between EMPLOYEE and the entity EXPERTISE.
• Another one-to-many relationship is created between SKILL and EXPERTISE.

Name the new entities appropriately.

Self-referencing relationships

A self-referencing relationship allows users to combine information from different occurrences of the same entity. For example, to relate different employees in a company, an application program might combine data from different occurrences of the EMPLOYEE entity. A database user can then show employees and the managers they report to.

You may find more than one self-referencing relationship on a particular entity. If the relationships use the same keys, they are probably mirror images of each other. For example, MANAGES and REPORTS TO are two side of the same coin. Since they both use the same key and carry the same data, they are really one relationship.

Replace the self-referencing relationship with an entity if any of the following are true:

• If the self-referencing relationship carries data (for example, the date that the employee began to work for this manager)
• If you want to carry historical information (such as what managers an employee has had)
• If the self-referencing relationship is a many-to-many relationship

Replace the self-referencing relationship with an entity, specifying two relationships between the original entity and the new entity. These relationships can be one-to-many or one-to-one, depending on the logic behind them.

The following diagram shows how you might resolve a self-referencing relationship into an entity having two relationships with the primary entity: one one-to-many relationship and one one-to-one relationship. The new entity contains further information about the relationship between manager and employees.

If none of the above conditions apply, you can represent the relationship simply using a foreign key. In this case, the key of the manager would be carried as a foreign key in the EMPLOYEE entity. This approach will require fewer storage resources and therefore is recommended in those situations where it can be used.