1

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.

0

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.

  • The table with info with the details about T&C is in another database. The table T&C records only keeps user related info, like the versions of T&C user accepted to and the time stamp of when they clicked accepted for each version. – user59290 Jul 17 '18 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.