Garmaine Staff asked 2 years ago

Hibernate 4.3.9, JPA 2.1

Let's say I have a Student entity like below, and the underlying table tracks change history i.e. any change to a field in this table generates a new record, with the previous version marked logically deleted.

@Entity
class Student {

    @Id
    String id;

    // This field refers to the very first i.e. original student record
    // So it has the same value for every subsequent Nth student record generated by a change
    @OneToOne
    Student masterRecord; 

    //Flag indicating that the record is historic and logically deleted
    @Column
    boolean deleted;

    @?
    List<StudentSubject> studentSubjects;
}

Now, let's say this entity is in a many-many relationship with another entity, Subject and this is managed through a junction/cross-reference table. For any Student, the junction table always stores its master record's ID.

@Entity
class StudentSubject {

    @?
    Student student;

    @ManyToOne
    Subject subject;
}

(The definition of Subject doesn't seem relevant here, but I can provide it if needed)

How do I model the relationship between the Student and StudentSubject entities (preferably in a JPA-compatible way but not a deal-breaker)? I've considered the following approaches:

1) Use a bi-directional ManyToOne / OneToMany relationship – @ManyToOne on StudentSubject.student, with referencedColumnName=masterRecord. This would break because there can be many Student records with the same masterRecord value.

2) Same as (1) but with a JoinFormula which adds deleted=false as an extra join condition. This would seemingly work in normal cases, but what if I wanted to look up a historic Student record ? With the extra condition added, the relationship wouldn't select anything. Also, not JPA-compatible. It'd produce the following join:

Student s join StudentSubject x on x.student=s.masterRecord AND s.deleted=false

3) Two unidirectional relationships – @OneToMany on Student.studentSubjects with @JoinColumn(name=masterRecord, referencedColumnName=student) and a @ManyToOne on StudentSubject.student with @JoinColumn(name=student, referencedColumnName=id). This would basically produce TWO separate joins –

  • Student s join StudentSubject x on s.masterRecord=x.student
  • StudentSubject x join Student s1 on x.student=s.id

This would work as expected in every situation but it's a terrible idea to have two relationships on the same column.