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
  • 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:


  • 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


  • 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