Refining the Database Design › Eliminating Unnecessary Relationships

Eliminating Unnecessary Relationships

The purpose of a relationship is to represent integrity rules between entities. As such, they serve a useful purpose in modeling your business. However, there is always overhead associated with a relationship. Since the DBMS must ensure the integrity of a relationship during update operations, they result in increased CPU and I/O. They may also require additional storage space.

While you should not sacrifice needed integrity, you should eliminate relationships that are not required for business reasons. Particularly review the need for:

• One-to-one relationships

  For example, the DEPARTMENT-HEAD relationship may not require DBMS enforcement of integrity and, if so, should be eliminated as a relationship.

• Relationships in which there are only a few pre-established parent occurrences

  Examples of this type of relationship would be STATE-OFFICE or SEX-EMPLOYEE. Ensuring that each office is in a valid state or that each employee is assigned a valid sex should be done in one of the following ways rather than as a relationship.

  • By using a map edit or code table (application enforcement)
  • By using a check constraint (in SQL-defined databases)
  • By using database procedures (in non-SQL defined databases)
  • Through a logical record facility path (in non-SQL defined databases)

In the Commonweather database, the relationship between INSCO and HEALTH INSURANCE PLAN can be removed.