Curent status
...
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
...
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 catchementcatchment)
1 live table, 1 log table
When a record is created, it is created in both table
When a record is modified, it is created in log table and modified in the live table
when a record is deleted, it is only on the live table
Strengths | Weaknesses |
|---|---|
when using the live table, the historical value won’t be an issue full llist available in the log table | 2 tables should be managed duplicate data can be dificult to find the active record at a given time (can use TOP + order date creation ASC but it works only 1 reesult) |
Opportunities | Treats |
cluster index on the UUID + version (creation date … ) for the log table schema could block the editing right of the log table database trigger might be used for duplication | the queries can point to the wrong one |
1 valid table, 1 old table
When a record is created, it is created in live table
When a record is modified, the old version is moved to the log table and modified the live table
when a record is deleted, it is moved on the live table
Strengths | Weaknesses |
|---|---|
when using the live table, the historical value won’t be an issue avoid data duplication | 2 tables should be managed UNION required to have the full list |
Opportunistes | Treats |
cluster index on the UUID + version (creation date … ) for the log table schema could block the editing right of the log table during the mode the “Validity to” can be added in the old table | record might be modified in the live data by modules without creating old record |
1 table with validity dates
When a record is created,
When a record is modified,
when a record is deleted,
...
Strengths
...
Weaknesses
...
easy to find the record valid at a given date
...
big table
difficult to manage clustered indexes
cannot have futuree value because the valitidy to as null is used to find line data
...
Opportunistes
...
Treats
...
Managed this on the front end to enable futur value too
1 table with active column
When a record is created,
When a record is modified,
when a record is deleted,
...
Strengths
...
Weaknesses
...
big table
...
Opportunistes
...
Treats
1 table with version increment
When a record is created,
When a record is modified,
when a record is deleted,
...
Strengths
...
Weaknesses
...
big table
...
Opportunistes
...
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