CODEWITHSUNDEEP
javadatabasehibernatedatabase-designnhibernate-mapping
joey_g216
joey_g216

Reputation: 796

Database design to track changes - w/ Hibernate

Hey All I'm having a difficult time with a database design. As you can see from my current design a Registration can have multiple EmployerRegistrations which can have multiple ClientRegistrations. It's pretty simple from here. A new registration needs to be created by the user each year.

Unfortunately I need to be able to track changes / amendments. Changes can be made to the Registration information (name, address, etc.) or Client Registration information (remove / add client or remove / add employer).

I've tried a bunch different designs, but so far nothing feels "right". Tracking amendments in the Registration table is easy as this affects all tables above it. All id's are updated. It's the changes to the ClientRegistration table that's throwing me for a loop. As you can see I have a version column I was trying, but it's not helping me much. With Hibernate it feels like each amended client registration needs it's own unique registration object, but creating a whole new registration for any client registration amendments doesn't seem right / efficient.

I've been battling this for about a week so any help would be greatly appreciated. Thanks!

enter image description here

Upvotes: 2

Views: 3386

Answers (3)

srini.venigalla
srini.venigalla

Reputation: 5145

Have you come across the concept of Temporal data modeling? You might want to Google for it. One very popular technique for temporal modeling is "Effective Dated" logic, used extensively in Peoplesoft. Briefly, it goes like this:

Every table in the system would have this design pattern:

    Table{
       Primary_key,
       effdt,
       effseq,
       other data,
       modified_ts
    };

Multiple versions of the record are "stacked up" using the primary key, effdt and effseq. Effdt stores date only, not datetime. effseq (int) is used store multiple changes on the same day. modified_ts stores the date stamp of data change.

the data in a table would look like this:

PrimaryKey1   2012-01-01  1  MyData1  MyData2
PrimaryKey1   2012-02-01  1  MyData1  Change1
PrimaryKey1   2012-02-01  2  Change2  Change1

To get the latest data from any table, you would use the query like this:

select * from MyTable A
where effdt = (select max(effdt) from MyTable where PrimaryKey = A.PrimaryKey)
and effseq = (select max(effseq) from MyTable where PrimaryKey = A.PrimaryKey 
                                                           and Effdt=A.EFfdt)

Will that help?

Upvotes: 0

John Ericksen
John Ericksen

Reputation: 11113

Have you checked out Hibernate Envers? Its an automatic versioning plugin for Hibernate. Makes tracking history of object changes very easy. It is configured in an AOP way, so you can simply annotate the object you want audited and let Envers handle the details:

@Entity
@Audited
public class Person {
    @Id
    @GeneratedValue
    private int id;

    private String name;

    private String surname;

    @ManyToOne
    private Address address;
    ...
}

Upvotes: 4

Bozho
Bozho

Reputation: 597056

You can use Envers, which is now bundled in hibernate-core. Check the docs

Upvotes: 2

Related Questions