0

I have a house-share entity( primary key : house-share id) and housekeeper entity (primary key: housekeeper id).

At any point in time a housekeeper can only work in one house-share.

When a housekeeper finishes an allocation with a particular house-share the allocation is recorded(start date and end date).

A house-keeper may return to a house-share they previously worked in; if they do so, a new allocation is recorded.

I believe the relationship between house share and housekeeper must be many to many. I can come up with a bridge entity(e.g. housekeepingwork) which have pk's housekeeper id, start_date and end_date. But usually a bridge entity will have primary keys from both entities but not in this case. I'm wondering if I'm modelling this wrong?

|improve this question
  • A bridge table is certainly needed. `housejob ( houseshare_id, housekeeper_id, job_start, job_end) with 2 FKs. – Philᵀᴹ Apr 27 at 13:08
  • I wasn't sure if a bridge entity can have primary key which does not completely consist of the primary key of related entities. Also, for this example, would housejob(houseshare_id,housekeeper_id, start_job) be enough for a primary key?Since no housekeeper can start working on two houses at the same time? – Sook Lim Apr 27 at 13:15
  • It depends on whether the housekeeper is assigned in full days or partial days. With full days you could use a constraint. Partial you can enforce with a trigger – Philᵀᴹ Apr 27 at 13:20
  • Alternatively, can the primary key be just housekeeper_id and start_date?(Assuming they are assigned in full days). – Sook Lim Apr 27 at 13:24
  • I think you’re a bit confused about PKs. I’ll write more when I get back home – Philᵀᴹ Apr 27 at 13:27

Your Answer

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

Browse other questions tagged or ask your own question.