...
For example, if a facility has the following attributes when instantiated:
Facility
| FacilityName | FacilityId | Type | LatCoord | LonCoord |
|---|---|---|---|---|
| Steinbach Hospital | F123 | Hospital | 30.113 | 19.210 |
and it is updated to be:
Facility
| FacilityName | FacilityId | Type | latCoord | lonCoord |
|---|---|---|---|---|
| Steinbach Hospital | F123 | Clinic | 30.111 | 19.210 |
There should be a record of the change, when the change occured, and who made the change. That is, our original table should be extended to show the following state data:
Facility
| FacilityName | FacilityId | Type | LatCoord | LonCoord | CreatedBy | CreatedDate | LastModBy | LastModDate |
|---|---|---|---|---|---|---|---|---|
| Steinbach Hospital | F123 | Hospital | 30.113 | 19.210 | KevinCussen | 2016-01-01 01:01:01 | null | null |
When the change is made, this table row should be updated to reflect the new data. For example, if John Smith has made the changes to Steinbach Hospital record on January 20th, the updated record would show the following:
Facility
| FacilityName | FacilityId | Type | LatCoord | LonCoord | CreatedBy | CreatedDate | LastModBy | LastModDate |
|---|---|---|---|---|---|---|---|---|
| Steinbach Hospital | F123 | Clinic | 30.111 | 19.210 | KevinCussen | 2016-01-01 | JohnSmith | 2016-01-20 12:34:56 |
Additionally, the previous state of the record should be recorded as a historical entity. Any time an update is made to a record, a log of the previous state should be persisted. One approach is as follows (though no approach is proscribed):
Facility_StateChangeRecord
| FacilityName | FacilityName_ChangedTo | FacilityId | FacilityId_ChangedTo | Type | Type_ChangedTo | LatCoord | LatCoord_ChangedTo | LonCoord | LonCoord_ChangedTo | CreatedBy | CreatedBy_ChangedTo | CreatedDate | CreatedDate_ChangedTo | UpdatedBy | UpdatedBy_ChangedTo | LastModDate | LastModDate_ChangedTo |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Steinbach Hospital | null | F123 | null | Hospital | Clinic | 30.113 | 30.111 | 19.210 | null | KevinCussen | null | 2016-01-01 | null | null | JohnSmith | null | 2016-01-20 12:34:56 |
Pengfei - Are you familiar with Envers? If this is a feasible solution, Mary Jo Kochendorfer and I will work through our ERD model and define which classes and class variables should be audited. Please schedule some time to discuss with me.
...