Recursion

A recursive relationship is a rule that exists between instances of the same entity. A one-to-many recursive relationship describes a hierarchy, whereas a many-to-many relationship describes a network. In a hierarchy, an entity instance has at most one parent. In a network, an entity instance can have more than one parent.

Let’s illustrate both types of recursive relationships using Employee. See Figure 7.5 for a one-to-many recursive example and Figure 7.6 for a many-to-many example.

Figure 7.5 An Employee may work for one Manager

·         Each Employee may manage one or many Employees.

·         Each Employee may be managed by one Employee.

Using sample values such as Bob and Jill and sketching a hierarchy or network can really help understand, and therefore validate, cardinality. In Figure 7.5 for example, where the one-to-many captures a hierarchy, each employee has at most one manager. Yet in Figure 7.6 where the many-to-many captures a network, each employee may have one or many managers, such as Jane working for Bob, Ken, and Sven. (I would definitely update my resume if I were Jane.)

Figure 7.6 An Employee may work for one or many Managers

·         Each Employee may manage one or many Employees.

·         Each Employee may be managed by one or many Employees.

It is interesting to note that in both figures, there is optionality on both sides of the relationship. In these examples, it implies we can have an Employee who has no boss (such as Mary) and an Employee who is not a manager (such as Jane).

Data modelers have a love-hate relationship with recursion. On the one hand, recursion makes modeling a complex business idea very easy and leads to a very flexible modeling structure. We can have any number of levels in an organization hierarchy in Figure 7.5, for example. On the other hand, some consider using recursion to be taking the easy way out of a difficult modeling situation. There are many rules that can be obscured by recursion. For example, where is the Regional Management Level in Figure 7.6? It is hidden somewhere in the recursive relationship. Those in favor of recursion argue that you may not be aware of all the rules and that recursion protects you from having an incomplete model. The recursion adds a level of flexibility that ensures that any rules not previously considered are also handled by the model. It is therefore wise to consider recursion on a case-by-case basis, weighing obscurity against flexibility.