Changing many-to-many relationships into entities

When you have attributes associated with a relationship, rather than an entity, you can change the relationship into an entity. This situation sometimes arises with many-to-many relationships, when you have attributes that are particular to the relationship and so you cannot reasonably add them to either entity.

Suppose that your parts inventory is located at a number of different warehouses. You have drawn the following diagram.

The Part and Warehouse tables.

But you want to record the quantity of each part stored at each location. This attribute can only be associated with the relationship. Each quantity depends on both the parts and the warehouse involved. To represent this situation, you can redraw the diagram as follows:

The Part, Inventory, and Warehouse tables.

Notice the following details of the transformation:

  1. Two new relations join the relation entity with each of the two original entities. They inherit their names from the two roles of the original relationship: stored at and contains, respectively.
  2. Each entry in the Inventory entity demands one mandatory entry in the Part entity and one mandatory entry in the Warehouse entity. These relationships are mandatory because a storage relationship only makes sense if it is associated with one particular part and one particular warehouse.
  3. The new entity is dependent on both the Part entity and on the Warehouse entity, meaning that the new entity is identified by the identifiers of both of these entities. In this new diagram, one identifier from the Part entity and one identifier from the Warehouse entity uniquely identify an entry in the Inventory entity.

Do not add either a Part Number or Warehouse ID attribute to the Inventory entity. Each entry in the Inventory entity does depend on both a particular part and a particular warehouse.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0