2

I have a relationship I cannot get quite right in JPA. The implementation is Hibernate.

I have these basic entities:

  • Volunteer (personal details)
  • Area (information about work area)
  • Session (time slot - start & end time)

There's a many-to-many relationship between Volunteer & Session, volunteer_session, indicating which volunteers are willing to work when.

There's a many-to-many relationship between Volunteer & Area, volunteer_area, indicating which volunteers are willing to work in which areas.

There's a many-to-many relationship between Area & Session, area_session with an additional column indicating how many volunteers are needed in that Area during that Session.

So far, I have all the relationships set up correctly in JPA.

The next stage is the tricky but - Assignments.

Volunteers may be assigned to any Area they are willing to work in, during any Session they are willing to work.

The table structure would be something like this:

Assignment
----------
volunteer_id
session_id
area_id

Where volunteer_id and session_id comprise the primary key, and also a foreign key to the volunteer_session table. Also, volunteer_id and area_id should be a composite foreign key to the volunteer_area table.

I could post code here, but it's already a long question and up to the point I want to add Assignment, it's all OK. Will post if requested.

NickJ
  • 9,380
  • 9
  • 51
  • 74
  • How do you feel about using a OneToOne on all three IDs and just imposing a unique constraint and/or index at the database level and populating this table based on queries/code enforcing the constraints in the other table? It sounds like you're imposing the constraints from the other tables in this assignment table using multiple foreign key/primary constraints. – Keefe Roedersheimer Feb 05 '16 at 18:28
  • 1
    [This answer](http://stackoverflow.com/a/31389630/4754790) could be useful, at least as a starting point. Another thing, if you want to reference the relationship table from other Hibernate entities, then it may be best to promote it to a new separate entity, and reference it in one-to-many associations from both of the sides that participate in the many-to-many association. – Dragan Bozanovic Feb 05 '16 at 18:31

0 Answers0