2007-06-22 Sync Conference Call
Next Tasks
Darius, Christian, Julie (out of town Mon - Thu):
follow up on the discussion with Burke on the approach to history:
detail the impact to DB schema by introducing _history tables
detail the impact of 'voided' cleanup (DB and API)
detail the impact to API from above
propose representation of _history data in API
demonstrate above using: person, concept, obs, encouter_type (or location)
Anders, Maros:
continue in parallel with sync work:
conclude timestamps ordering
come up with proposal for unique PKs (i.e. GUIDs or mapping existing PKs)
skeleton of sync transfer mechanism, service interface
Discussion Summary
on call: darius, christian, anders, julie, burke, maros
history and auditing discussion:
Burke on wiki notes about xxx_history:
pretty straight forward, looks OK, but hopped to have this isolated to 1-2 tables; is doubling our table count necessary?
what is the impact to developers? does this (i.e. auditing) 'get' in the way of people writing API code? Auditing should just happen and the implementation should not dependent on developers writing services code in certain way to make this work
therefore: it seems the generation of the audit trail then belongs to the data access layer (DAO), not service layer
Can hibernate help with this? Perhaps – there is audit interceptor pattern out there. we should be able to do this via Hibernate's eventing system.
what if we did triggers to begin with: ideally not; let's stick to something that is RDBMS independent; Hibernate is one level up from physical DB, ideally we would get it working there
how do we present data history consistently to the end user?
current 'void' concept is not ideal, something better will be needed
Should history be mandated?
if we remove voided from the current tables then we essentially mandate having history tables: 'voided' was added to the tables that have it today because they required auditing – if we consolidate and remove the columns; thus minimally tables that today have 'voided' must have history
What is the action in the history table, do we need 'deleted'?
JUST CRUD?
do we need to support purge record?
i.e. physical deletion of the record and its children, and perhaps history.
agreement: we are OK if this is implemented as outside function: this is done via module at only certain circumstances
Void, delete, purge discussion
What do these terms mean? Common definition:
*void:
from UI perspective, user is 'deleting' the record
we keep physical record, but set a flag
service APIs do not by default include voided
we should keep the record in main table and the history record of change in flag value
*purge:
complete remove of the record from main and history tables
*delete would than be:
remove from the main table, but keep the history
'voided' column as of today is used fro both 'void' and 'delete' semantics as defined above
primary intent was as 'delete', however in absence of having history tables, we ended up using this mechanism
another usage of 'voided' today: void a concept and do not have to delete all FK records
As a result, let's introduce*status:
it describes the state of the record from application semantics perspective such as "active, inactive, retired, suspended"
the record is still in the main table, change of status tracked as a field change in history
concrete example: concept
if we want to stop using existing concept that has tons of obs already we would change status (i.e. status='retired')
if we just entered one by mistake and no assoc obs were entered; then 'delete' is desired
status value space: are these common to all tables?
ideally, but given the status defined by application data semantics it is likely that different domains (i.e. orders) may require status values that do not apply universally: start with common value space, create specific extensions (i.e orders_status) as/if needed