3.1 Historical Data Management
1. What is the purpose of historical data?
- Analyzing audit fields for security reason
- Tracking data changes
2. Schema Design for Historical Data
- All collections include a sequential number field
- All collections include an audit trail
- Historical collections store data on different physical space (collection)
- Historical collections include full document of active collection
2-1. what is audit trail: a security-relevant chronological record (Wikipedia)
Example:
CREATE_PROGRAM_ID: application id that creates data
CREATE_OBJECT_TIME: created time
UPDATE_PROGRAM_ID: application id that updates data
UPDATE_OBJECT_TIME: updated time
ARCHIVE_PROGRAM_ID: application id that archive data
ARCHIVE_OBJECT_TIME: archived time
ARCHIVE_STATUS_CODE: archive yes/no (*: be subject to archiving)
2-2. Example of Historical Collection
[Active Collection]
{
_ID: "1", // 1) increment version number
GTIN: "1000000001",
NAME: "RED",
...
CREATE_PROGRAM_ID: "CreateGtinPgm", // 2) Audit fields
CREATE_OBJECT_TIME: "2018-01-01 21:00.36",
UPDATE_PROGRAM_ID: "UpdateGtinPgm",
UPDATE_OBJECT_TIME: "2018-01-05 21:00.36",
ARCHIVE_PROGRAM_ID: "PurgeDataBatPgm",
ARCHIVE_OBJECT_TIME: "2018-04-05 21:00.36",
ARCHIVE_STATUS_CODE: "*"
}
[Historical Collection]
{ // 3) different physical space
_ID: 1,
_ID_ORIGIN: 1, // 4) full document of active collection
GTIN_ORIGIN: "1000000001",
NAME_ORIGIN: "RED",
...
CREATE_PROGRAM_ID_ORIGIN: "CreateGtinPgm",
CREATE_OBJECT_TIME_ORIGIN: "2018-01-01 21:00.36",
UPDATE_PROGRAM_ID_ORIGIN: "UpdateGtinPgm",
UPDATE_OBJECT_TIME_ORIGIN: "2018-01-05 21:00.36",
ARCHIVE_PROGRAM_ID_ORIGIN: "PurgeDataBatPgm",
ARCHIVE_OBJECT_TIME_ORIGIN: "2018-04-05 21:00.36",
ARCHIVE_STATUS_CODE_ORIGIN: "*",
CREATE_PROGRAM_ID : "CreateHisPgm",
CREATE_OBJECT_TIME : "2018-01-01 21:00.36",
UPDATE_PROGRAM_ID : "UpdateHisPgm",
UPDATE_OBJECT_TIME: "2018-01-05 21:00.36",
ARCHIVE_PROGRAM_ID : "PurgeDataBatPgm",
ARCHIVE_OBJECT_TIME : "2018-04-05 21:00.36",
ARCHIVE_STATUS_CODE : "*"
}
3. Managing Historical Data: implement functionalities in application level
- When the application inserts new rows in progress table, then it inserts the rows to history table at once.
-
When the application update the existing rows in progress table, then it inserts the rows to history table at once.
-
Using the gathering data in historical table, we can track data changes history and analyze audit fields for security reason.
This page will be about historical data management
Managed by schema designs: Store full document with increment version number field (possibly extra field to ID latest version). Store all document versions in one document. Store old versions in separate collection Store only deltas(versions witch changes) with each increasing version. Derive current state by merging.