The following image shows that each department can be associated with several employees and locations, and each location can be associated with several departments and employees; however, each employee is associated with a single department and location. Ternary relationship

Question:- what if I want to depict that an employee can work at multiple departments but only at a single location? i.e., I want to represent a many to many relationship between employees and departments and between departments and locations but a many to one relationship between Employees and Locations

  • 1
    Your depiction shows the possibility of several many to many relationships, not that there are such relationships. An ERD would be much more effective in conveying the relationships between entities. Even so, depending on the business model, an employee could possible work out of multiple locations and departments - think education or medical. – Steve Mangiameli Sep 9 '15 at 16:14
  • Thinking on your question a little more, if you only want to show an employee in a single location, remove the line connectiong Employee to Works_in3 and connect Employee to Locations. One location per employee would result in a one to many from Location to Employee via LocationID. – Steve Mangiameli Sep 9 '15 at 17:03

This is a great question that shows the importance of breaking down n-ary relationships based on the business rules contained in them and is a good example of the application of forth normal form which deals with multi-value dependencies (MVD).

The following business rules exist:

  1. Each employee may be assigned to one or more departments.
  2. Each department may be composed of one or more employees.
  3. Each department may be operating in one or more locations since some point in time.
  4. Each location may be an address of one or more departments since some point in time.
  5. Each employee must be found in one and only one location which is by definition a location that is an address of a department the employee is assigned to.
  6. Each location may be an address of one or more employees.

Following is an ERD in Barker-Ellis notation created using Oracle SQL Developer Data Modeler showing these rules:

enter image description here

The key to the solution is to decompose the ternary relationship into the binary relationships supported by the business rules. Those were the many to many relationships between employees and departments, and between departments and locations. Then, implement the one to many relationship between location and employees that shows that, while an employee may be assigned to many departments, they can be found at only one of that department's many locations.

Note that it is not possible to depict the portion of rule 5 above that states "...which is by definition a location that is an address of a department the employee is assigned to." This is a good example of the limitations of ERDs. Their strength is the ability to visually depict entities and their relationships, and their weakness is they cannot depict more complex business rules beyond relationships. This is why it is vital to investigate and discover all the business rules and not just the referential rules governing associations. Fabian Pascal's paper Business Modeling for Database Design provides excellent detail on all the necessary rules to explore. Ronald Ross has generated a large body of work on this topic as well. Chris Date also wrote a short book on business rules.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.