Toplink, Many-to-many relationship with date dependency
Joined: Oct 22, 2002
We have an application which manages membership of users in groups.
It is typical many-to-many relationship (table users and groups and
relational table usersgroups). So far so good.
But we would like to introduce another condition into this relationship
- date validity.
For example - person should belong to group only from date1 to date2.
I think that relational table should contain not only a couple of id's but also
a two dates (from-to) and all those four together should form primary key.
Then we should need a condition (where) for such relationsphip.
But toplink many-to-many mapping doesn't have any features for this.
Have you any idea how to implement our requirement in another way?
Joined: Oct 01, 2007
Generally when your join table has more columns in it you need to map it as an object.
TopLink (EclipseLink) does have support for historization, and a ManyToManyMapping can make use of a HistoryPolicy with a start/end date.
Normally history would be used for the entire object/session, but you may wish to investigate it.
You can also set the selectionCriteria on a mapping to use more advanced criteria than foreign keys.