Mysql – @OneToMany mapping but only one is needed

hibernatejavaMySQLrdbms

I have to create a new table to store version numbers of Terms&Conditions user have accepted and the time stamp of when they click accept. (This table only contains info related to user. Info about the T&C version itself is stored in another table)

That means user A could have accepted 3 versions of T&Cs that has been updated over time. The only version I care most about is the latest version the user has agreed to, but I still need to keep the history of accepted versions for integrity’s sake.

Scenario:

  1. I will already have the user entry in the database before the version records get stored.

  2. There can be users that has not agreed to any version of T&C.

  3. 1 User may have accepted several version of T&C over time

  4. I want to retrieve only the latest version user has accepted along with the user object with minimal step taken.

  5. If possible when I delete the user I would like it if ALL accepted version records for that user also get deleted (foreign key on cascade delete or something)

How can I implement a clean solution for this? I have come up with two possibilities but I’m not sure if it’s any feasible.

Solution 1: @OnetoOne unidirectional relationship with User keeping the FK reference to the latest T&C record the user has agreed to. The T&C record will keep user_id as non primary and non unique FK field.

@Entity
@Table(name = “user”)
public class User {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name =“user”)
  Long id;

  @Column(name = “username”)
  private String username;

     .........

  @JoinColumn(name = “terms_record_id”)
  @OneToOne(cascade = CascadeType.ALL)
  private TermsRecord termsRecord;
  // joined on terms_record_id to get only the latest version accepted
}

And the entity to record version user has accepted

@Entity
@Table(name = “terms_record”)
public class TermsRecord {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = “terms_record_id”)
  private Long id;

  @Column(name = “agreed_on”)
  private Date agreedOn;

  @column(name =“version_no”)
  private String versionNo;

  // I’m not sure if it needs to have userId in here as well?
  @column(name = “user_id”)
  private Long userId;

   ........ 
}

And in the DB

create table user {
   user_id bigint as Primary Key,
   ...
   terms_record_id FOREIGN KEY references terms_record(terms_record_id);
   // to refer to the most updated version
}

create table terms_record {
   terms_record_id bigint NOT NULL,
   user_id bigint,
   PRIMARY KEY(terms_record_id),
   FOREIGN KEY(user_id)  REFERENCES User(user_id)            cascade on delete     
   // to keep the records of terms user has agreed to
}

In the code, it will be something like:

User userA = getUser(user_id);
TermsRecord tr = new TermsRecord();
tr.setVersionNo(“1.0.5”);
tr.setAcceptedOn(....);
userA.setTermsRecord(tr);
save(userA);

So in this way, if user A had accept to terms version 1.0.3 before, it won’t delete the entry from terms_record table but it will pull a different versionNo when you retrieve userA from database.
Eg. 

userA.getTermsRecord.getVersionNo == 1.0.5

And In terms_record table

  terms_record_id |     version_no
______________________________________
..........        |        .........
    3             |         1.0.3
    5             |         1.0.5
 ........         |        ..........

Solution 2

Unidirectional @OneToMany annotation in user class
With foreign key user_id stored in TermsRecord, and cascade on delete. Then retrieve the latest version manually.

Best Answer

Plan A: Have a flag in the T&C table saying which is the latest. This is clumsy to maintain, and may not meet the criteria of some entity being held by an older T&C.

Plan B: Move the antiquated entries to another table.

Plan C: The link (JOIN) from entity to T&C involves 2 columns: which T&C, plus which version of it. This may be the best version.

Related Question