3

I want to create an entity relationship diagram for a three way relationship in the following contrived situation:

Each COMPANY has many DEPARTMENTS. An EMPLOYEE can work simultaneously for many DEPARTMENTs. However, an EMPLOYEE can work for only one DEPARTMENT of a COMPANY. In other words, the EMPLOYEE works for as many DEPARTMENTS as COMPANYs.

What kind of associations and relationships will such a diagram have? My purpose is not to create tables in a database but just as an aid to understanding.

Thanks in advance for your help!

Anne Forumer
  • 83
  • 1
  • 4

1 Answers1

1
COMPANY_EMPLOYEE_DEPARTMENT:
    COMPANY (PK)
    EMPLOYEE (PK)
    DEPARTMENT

Each combination of company and employee, can only be associated with one department. That is, an employee can only work for one department in each company.

This should work as long as a department does not belong to more than one company, and that the COMPANY_EMPLOYEE_DEPARTMENT relation does not link a department of one company with another company.

Markus Jarderot
  • 86,735
  • 21
  • 136
  • 138
  • Yes, unfortunately this schema is denormalized: department already has a reference to company (I guess). However I can't think of anything better... – Tomasz Nurkiewicz Jul 07 '12 at 20:14