Database design - Entity Relationship Model

I have the following problem that I need to express.

There are persons, workplaces and sites. Each person can be assigned to multiple workplaces. Each workplace can have multiple persons. Each workplace has exactly one site. So far so good. But my problem is that each person has only one workplace at a specific site.

How can I express this in an ERM?

My idea so far:

I just can't express the "one person has only one workplace at a specific site"-problem with this approach.

Implementation Solution:

Table Person with Prs_ID (PK)
Table Site with Site_ID (PK)
Table Workplace with Plc_ID (PK)
Table Per开发者_如何学Goson_Site with Prs_Site_PrsID (PK, FK), Prs_Site_SiteID (PK, FK), Prs_Site_PlcID (FK)
Unique Index on Prs_Site_PlcID

I think this should solve the problem. Now how can I express this in an ERM?

Edit:

I thought it would solve the problem but it doesn't. With this I can't assign one workplace to two different persons, because there is a unique index on the Prs_Site_PlcID column. Back to the beginning...

---

Note unique index Ak1 (alternate key) (SiteID, WorkplaceID) on Workplace which is propagated to PersonWorkplace.

--
-- PostgreSQL
--
create table Site      (SiteId      integer not null);
create table Person    (PersonId    integer not null);
create table Workplace (WorkplaceID integer not null, SiteID integer not null);
create table PersonWorkplace
(PersonID integer not null, SiteID integer not null, WorkplaceID integer not null);

alter table Site   add constraint pk_Sit primary key (SiteID);
alter table Person add constraint pk_Prs primary key (PersonID);

alter table Workplace
  add constraint  pk_Wpl primary key (WorkplaceID)
, add constraint fk1_Wpl foreign key (SiteId) references Site (SiteId)
, add constraint ak1_Wpl unique (SiteID, WorkplaceID);

alter table PersonWorkplace
  add constraint  pk_PrsWpl primary key (PersonId, SiteID)
, add constraint fk1_PrsWpl foreign key (PersonId) references Person (PersonID)
, add constraint fk2_PrsWpl foreign key (SiteID, WorkplaceID) references Workplace (SiteID, WorkplaceID);

---

I think the clue is in the question.

You say that each work place has one site - the relationships thus are:

many persons have many workplaces

One workplace has one Site;

Implementation suggestion:

Person table
-----------
person_id primary key
.....

Workplace table
--------------
workplace_id primary_key
site_id (unique index)

person_workplace table
-------------------
person_id
workplace_id

site table
--------------
site_id primary key

The unique index on the site_id column in the workplace table ensures that each workplace is associated with a different site.

---

YOu need an intermediate table EMPLOYEES which represents a PERSON's stint at a worksite working for an employer. A single person can be many employees, i.e. work for more than one employer. Day job / night job, or jobs in succession. EMPLOYEE is not a synonym for the person entity but is a representation of person-at-employer.