Hello everyone, I have been fighting with defining a class with effective dates as part of the join table and just cannot figure out how to make it work. I have search all over the net for clues, but have yet to find anything such like this. If anyone has experience with this, I would really love to hear your thoughts.

I have the following tables:

PRODUCT
PRODUCTID - PK
... more

CATEGORY
CATEGORYID - PK
... more

CATEGORY_MEMBER
PRODUCTID - PK
CATEGORYID - PK
FROM_DATE - PK
THRU_DATE
SEQUENCE_NUM

This way the same product can be a member of the category at different times. I just for the life of me cannot figure out how to annotate my class (Product for example) with the ManyToMany relationship to support this join table.

I have tried creating a new entity class ProductCategoryMember and defined the fields there.

In my product class I defined the join as:
@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(name = "CATEGORY_MEMBER", joinColumns = {@JoinColumn(name = "PRODUCTID")},
inverseJoinColumns = {@JoinColumn(name = "CATEGORYID")})

When the table is created however, the FROM_DATE is not part of the PK. I tried adding FROM_DATE to the inverseJoinColumns as well, but that just threw an exception.

Also, how do I tell the relation to order the results by the join table's sequence_num?

Any help would be greatly appreciated. Thanks!