I am cheating just a bit with this challenge, as this is one of the ones from my new book Data Modeling Made Simple— but I thought it would be fun to share with the group:
Assume there is a rule in your company that an employee can work for one and only one manager. A modeler you work with is working on a human resource logical data model and has modeled the management hierarchy as shown in the following entity. She would like to know your thoughts on this. What are the pros and cons with using this entity to represent this business rule?
By the way, our raffle winner for this challenge is Eric
Wilson. Congratulations Eric! Eric, email me your address for a complimentary signed copy of Data Modeling Made Simple!
We are given the following logical model and asked to explain the pros and cons of using this model to represent the business rule:
An employee can work for one and only one manager.
I’ll briefly cover what I see as the pros and cons. I’ll also throw in a few additional comments before hearing from the group.
1. Flexibility. This model handles the required rule, as well as any other relationship between Employees. For example, the following occurrences are all handled very nicely:
- Bob works for Mary.
- Bob works for Mary and Jim.
- Mary has Bob and Carol has direct reports.
If employees can suddenly work for a second manager (the second example above), the model supports this.
2. Fast. If the modeler does not have the time to confirm the complete set of business rules, or if knowledgeable business folks and requirements documents are not available, this model guarantees that regardless of the actual rules the model and resulting application will accept them without requiring costly model and application rework down the road. This model can be created quickly and we know that the resulting solution will encompass all possible business rules. In fact, many-to-many recursive relationships are the almost sure way to guarantee that for a minimal amount of time modeling you will have the correct solution. Remember that Thing entity with a many to many that could model the world? :o)
The first thing to ask yourself before embarking on any modeling effort is “What will be the purpose of this model?” A logical data model needs to represent business rules independent of contextual factors such as hardware, software, and usage. It also represents a snapshot and not a cross section over time. I believe in modeling the existing rules exactly as they are and then looking for areas to build in flexibility. So if someone showed me the employee recursive entity, I would respond with something like “Can I see the model that represents all of the existing rules first, and then please explain the business case why such as generic relationship such as this is needed.” If questioned by the modeler why first representing the existing rules and then introducing generic concepts is even valuable, I would mention some of the cons to this structure and that modeling is an art in tradeoffs. We must proof the value of such a generic relationship, a value that must be greater than the following costs:
- Model communication is reduced. One of the main purposes of building a model is for understanding and communication, and a recursive relationship is always a difficult subject to explain and “touch”.
- Obscuring actual business rules. There could be lots of actual rules hiding in this relationship and we are not doing our due diligence as analysts to find out what all of them are. Some of the rules might have large implications in the physical design.
- Data quality can be compromised. If the physical data model is very much like this logical data model, we can relate dogs to cats and the model will support this. Lonna’s comments below bring up the point that we can inadvertently even relate the employee to himself or herself. We can enforce some rules through database triggers or code, but I can vouch firsthand that it is not foolproof.
Some additional thoughts
A number of responses to this design challenge stressed that a pro to this generic relationship is that it can support employees reporting to other employees over time. That is, Bob used to work for Mary and now reports to John. One of the tricks to this design challenge is that this is a logical data model, and therefore we do not need to yet concern ourselves with history (aka slowly changing dimensions).
A number of responses below challenged the original business rule. For example, Jeff’s comments on how to handle the CEO (where someone does not theoretically have a boss) or Bob’s comments where managers many not have direct reports. I would definitely challenge the business with these kinds of questions and depict all of the rules on the logical, using generic concepts only where I can prove there is value.
There were some great responses to this design challenge, and several responses touched on similar points. For brevity purposes, I shortened comments and if responses were very similar I usually included the first one submitted. Responses are listed alphabetically by contributor.
First I would resolve any many:many relationships in a logical. Second, the “reports to” verb is only one side of the relationship, you also need the “manages” verb.
The model represents a many-to-many relationship between Employees. Which means, for a relational implementation, there must be an intersection entity. No foreign keys can be stored in the Employee record. This gives some flexibility if the rule of “at most one boss” gets relaxed or has some exceptions in the future. If the company can truly say they want to enforce the “one boss” rule, then the model needs to be revised, and the relationship can be represented by storing a foreign key in the Employee table pointing to the Employee who is the boss of that employee.
This model does not support the business rule at all. Based on the many-to-many relationship, this model states that a manager (which is a type of employee) can have 1-to-many employees, but it also states that an employee can have 1-to-many managers. It actually doesn’t even say that much. All it says is that an employee reports to 1-to-many employees. The only pro I can think of for this approach is that it is very flexible and allows the business to change the business rules whenever they want, as long as the application has that flexibility. The cons are numerous.
1. It doesn’t support/enforce the business rule
2. It allows any employee to have people reporting to them.
3. It even allow an employee to report to themselves.
4. It is totally dependent upon the application to enforce any business rules.
5. It really doesn’t tell you anything about the relationship between the different roles that employees play.
First of all, a many-to-many relationship cries out for an intersect entity. Yes, you also violated the company rule, but the rule was incomplete. It should have been, “an employee can work for one and only one manager–at a time”. If you add the intersect entity, it’s attributes can then include “Start date” and “End date”, thereby allowing the poor sap involved to eventually get a promotion (or a new job).
If I’m reading this correctly then this must be a logical construct because it can’t be implemented physically: it’s a snake eating it’s own tail. More accurately, it’s a snake with a head at each end and the heads are eating each other. This many-to-many recursive structure certainly doesn’t meet the business rule of single parentage. Change one of the manys to a single to form the standard recursive bill-of-materials structure and you’ve met the requirements.
If historical working relationships are not needed nor maintained, the model’s relationship should be a recursive one-to-many with “supervises” as the relationship from parent to child and “reports to” as the relationship from child to parent. (Yes, this is being picky, but a rule is a rule). This would properly depict the business rule as written, but (and here’s the “con” of this option) the relationship would need to be optional in both directions (the CEO does not “report to” another and the lowest employee on the totem pole does not “supervise” another). This would require some form of implemented logic to maintain the business rule for all but the CEO (or whomever is at the top). NOTE: I can hear it in the minds of all the slick-trick programmers out there, so-o-o-o — DO NOT store the CEO row so that he reports to himself, and DO NOT add employee “00000000” or “99999999” for the CEO to report to!!
Bob Mosscrop III
Simple to depict (discounting the many-to-many comment above, its a straight recursion).
How do you handle the ultimate manager? Whom does that person report to? How about timing of the hiring (such that a new employee does not yet have a manager assigned) – how would that be handled (cannot handle optionality)? What about the reciprocal relationship (“manages”) – how would that be handled. What if a manager does not have any employees (zero or one not handled as modeled – each employee must have a manager.
First, the rule was that an employee works for one and only one manager, and the relationship as shown is a many-to-many, which breaks the rule. Additional documentation would be needed to demonstrate that the M:M is to document the historical managers that an employee has had.
Second, the basic premise is flawed. What if the manager resigns and there is a gap in time before the replacement is hired? Do you leave the manager FK null for those employees that reported to the manager, or create some “dummy” manager record? No real elegant solution that way. There needs to be a distinction between the person and the role they are playing. The HR data model should certainly reflect this reality, that sometimes people report to a manager role that is currently vacant.
1. Allows for history – the rule may be one manager at any given time but it’s almost certainly many over time.
2. Allows for setting up future effective date relationships (given implementation using an associative entity with effective date attributes).
3. A concise, initial, high level representation.
1. Hides the rule that there is only 1 manager at a given point in time and without more detail suggests otherwise.
2. Relationship name is not quite correct but simpler.
3. To simple for any true understanding of what is meant or any useful implementation. An associative entity and effective date attributes are needed – assuming history and future effective dates are required.
First, I would further clarify with my business area whether there is any employee with NO manager -the president, for example. And on the flip side, aren’t there employees who don’t manage any other employees (the folks at the bottom of the hierarchy).
If these are both true, then one side of the relation needs to show zero, one or many, and the other side needs to show zero or one.
Eric M. Wilson
The relationship as shown, being many-to-many, does not constrain the situation as tightly as the business rule. Changing it to many-to-one would resolve that.
There are other considerations though:
1. The relationship itself is not sufficient to prohibit recursive loop-backs. Jim reports to Sally who reports to Sue who reports to Jim. Additional integrity constraints are necessary.
2. An alternative design could accomplish the current rule, but allow for simple changes to accommodate a more flexible org. structure in the future. Namely, separating out “Employee Reports To” as an entity separate from the Employee.