Content

Curent status

Management of the version

openIMIS use a type 2 database were a row is added when a record is changed; there is two reasons:

in openIMIS, the first record is valid for the date-time of entry. if a item (product/service/price list ) need to start before then the Validity

Auditing

This enable to see the historical record in order to see who made which changes

Manage multiple version of a record

For some tables the business logic will refer to version of a record valid at the time of claim submission

Management of the reference to versioned records

The reference use the rowid for the versioned Item which can ease the joining but complexify aggregations mostly for the Item that just need auditing.

Future status

Management of the version

With the modular architecture, mutation table were added into the picture, those table could easily answer the auditing needs but it is unlikely to be a suitable solution to manage the versioned record because recreating past record from the mutation will consume too much compute power.

Most of the Item that needs versioning might also need update without publishing a new version, on the other end new version validity date need to be configured by the users

List of table that needs versioning:

• Benefit package: required to assess claim visit type limit, ceiling, deductible …

  • Benefit package Services: required for assess Child / Adult ceiling, Amount limits, limit exclusiosn and relative pricing

  • Benefit package Items: required for assess Child / Adult ceiling, Amount limits, limit exclusiosn and relative pricing

• Medical Item: tblItems required to assess frequency and mask of service: Is it really needed ?

• Medical Product: tblService required to assess frequency, service type and category and mask of service: Is it really needed ?

• Medical Item Price List : needed to unsure the agreement update are taken in account for the right claims

  • Medical Item Price List Items details : tblPLItems

• Medical Services Price List: needed to unsure the agreement update are taken in account for the right claims

  • Medical Service Price List Service details : tblPLService

• Health facility (for price list, type of care and catchement)

• Location:Villages (for population)
  

Management of the reference to versioned records

The strategy to reference to versionned Item should be discussed

Constraints

• The solution should help the database persormance

  • either using different tables

  • either have field on which the valid row could be used to generate clustered index (Null value is not making it easy)

• The solution should enable the search on valid and historical event (could)

• not all table can be managed with a validity date for the underlying data (unlike catchment)

Solution proposed:

Use Reference/tag per Version and audit log (mutation or table) is used for subversion

Data management

• For the same reference, only one version can be valid for a given date (validation check to forsee)

• It will still use the validitFrom and validityTo fields, Validity to updated only when there is a new reference that have a validyFrom define or when it is manually updated.

• Reference must be unique for the same context (e.g. 1 ref per contract, or 1 ref location+period)

Database optimization:

• Cluster index per date created and ref.

• One Indexed table with active record

• What is the risk of activating the optimistic locking ?

Data flows:

• When a version is created, A unique reference must be created (uniqueness for context or across table), the ValidityTo is define either manually (to enable future dating) or automatically to the creation date

• When a version is modified ( sub-version is created ), the row is updated and a log entry is created

• when a version is deleted, the validity to is updated

• When a new reference is created to the same context, the validity to of the former reference is updated to the date of the validityFrom ofthe new reference

Child Pages