Entity Modelling

www.entitymodelling.org - entity modelling introduced from first principles - relational database design theory and practice - dependent type theory

Many-Many Relationships and Their Avoidance

A many-many relationship is one depicted with a crows foot at each end whereas a many-one relationship is one with a crows foot at one end but not the other. An example many-many would be:

An alternative to using a many-many relationship is to use two many-one relationships and an entity type. The result is shown in figure 6. Any entity type introduced to eliminate a many-many relationship in this way is called an intersection entity.

Figure 6
Students and courses in many-many relationship represented using an intersection entity enrollment.

The avoidance or elimination of many-many-relationships from models is important to database designers who wish to represent or communicate the facts of a situation in tabular form. After elimination of many-many relationships the tables that are required are just one table per entity type in the model. This topic is covered in chapter 5.

Instead of using a pair of reference relationships in the elimination of a many-many, one composition and one reference can be used instead as illustrated in figure 7.

Elimination by party within agreement — this is the model that feels most appropriate conceptually in that 'parties' are parts of agreements rather than parts of individuals.

Figure 7
Eliminating of a many-many relationship using composition.

When a many-many recursive relationship is eliminated using a pair of reference relationships then the resulting model has the shape shown in figure 8.

Figure 8
The shape after elimination of a many-many recursive relationship.

In regard to any model shaped as in figure 8, we can think of entities of the left hand type as being points and those of the right hand type as connections between these points; in this way we can imagine the instances of a many-many relationship as forming a network. For example because social interactions between people are many to many we can, and do, speak of social networks and in like manner we speak of business networks, supply networks, computer networks and so on; these are all usages based on many-many relationships between various types of entities as for instance this one:

Of course we don't need entity modelling to enable us to think of networks of this, that or the other; entity modelling simply gives shape (that of figure 8) to this practice - as we say elsewhere entity modelling gives shape to metaphor. In the next section we discuss the network concept and variations of it in the forms that they have been given by mathematicians. At the same time we look at what would be meant by replacing either or both of the two reference relationships in figure 8 by composition relationships.