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:
- Each employee may be assigned to one or more departments.
- Each department may be composed of one or more employees.
- Each department may be operating in one or more locations since some point in time.
- Each location may be an address of one or more departments since some point in time.
- 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.
- 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:
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.