How to have a log or history table for User in MySQL and Hibernate

Question

I am confused in creating a database design for my application. I am using MySql as database and spring and hibernate.

My requirement is: I have a user table with roles admin/user and I have a device table. In the device table I have a column called createdBy which is the userId who added the device. A user can update the device status, so every time a device value or status is changed, I need to log this. I am using a separate table called DeviceHistory to log this.

I am stuck in implementing this in hibernate.

• should I use the userId as int field in device table and device_history table?
• should I use OneToOne mapping o userId in Device table and deviceHistory table
• should I use OneToMany mapping for device and devicehistory table and OneToOne mapping from device to user table. Please share links if any sample code there for such scenario

User table - contains user details

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

    @Id 
        @GeneratedValue(strategy=GenerationType.AUTO)
        @Column(name="id", unique = true, nullable = false)
    @Column(name="user_id")
    private int userId;

    @Column(name="name")
    @Nonnull
    private String name;

    @Column(name="password")
    @Nonnull
    private String password;

    @Column(name="role")
    private String role;

    @Column(name="creation_date", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP", insertable=false, updatable=false)
    @Generated(GenerationTime.INSERT)
    @Temporal(TemporalType.TIMESTAMP)
    @Nonnull
    private Date creationDate;

    @Column(name="approved_date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date approvedDate;

    @Column(name="approved_by")
    private int approvedBy;

}

Device table: contains device details

@Entity
@Table(name = "device")
public class Device {

    @Id 
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="id", unique = true, nullable = false)
    @Column(name="device_id")
    private String deviceId;

    @Column(unique=true, name="serial_number")
    @Nonnull
    private String serialNumber;

    @Column(name="creation_date")
    @Generated(GenerationTime.INSERT)
    @Temporal(TemporalType.TIMESTAMP)
    @Nonnull
    private Date creationDate;

    @Column(name="created_by")
    private int createdBy;

}

DeviceHistory table: Here the history such which user updated device status and when is saved

@Entity
@Table(name = "device_history")
public class DeviceHistory {

    @Id 
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="id", unique = true, nullable = false)
    private int id;

    @Column(name="device_id")
    @Nonnull
    private String deviceId;

    @Column(name="notes")
    private String notes;

    @Column(name="last_modified_date")
    @Generated(GenerationTime.INSERT)
    @Temporal(TemporalType.TIMESTAMP)
    @Nonnull
    private Date lastModifiedDate;

    @Column(name="last_modified_by")
    private int lastModifiedBy;
}

Answer 1

You should use ManyToOne mapping in Device for User. Similarly, In DeviceHistory you should use ManyToOne mapping for DeviceId and lastModifiedBy.

Your Device and DeviceHistory entity should be:

@Entity
@Table(name = "device")
public class Device {

    @Id 
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="device_id", unique = true, nullable = false)
    private String deviceId;

    @Column(unique=true, name="serial_number")
    @Nonnull
    private String serialNumber;

    @Column(name="creation_date")
    @Generated(GenerationTime.INSERT)
    @Temporal(TemporalType.TIMESTAMP)
    @Nonnull
    private Date creationDate;

    @ManyToOne(optional = false)
    @JoinColumn(name="created_by")
    private User createdBy;

}

@Entity
@Table(name = "device_history")
public class DeviceHistory {

    @Id 
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="id", unique = true, nullable = false)
    private int id;

    @ManyToOne(optional = false)
    @JoinColumn(name="device_id")
    private Device deviceId;

    @Column(name="notes")
    private String notes;

    @Column(name="last_modified_date")
    @Generated(GenerationTime.INSERT)
    @Temporal(TemporalType.TIMESTAMP)
    @Nonnull
    private Date lastModifiedDate;

    @ManyToOne(optional = false)
    @JoinColumn(name="last_modified_by")
    private User lastModifiedBy;
}

You can consider bi-directional OneToMany mapping for User and Device if you want to get all the devices created by a user. For that you will have to use OneToMany annotation for devices in User entity.