Database migration in the openIMIS open source health insurance management system (Bachelor Thesis, Ongoing)

Author

Michel Borer

Description

The Swiss Tropical and Public Health Institute (Swiss TPH) is working with a global initiative (openIMIS Initiative, https://www.swisstph.ch/en/about/scih/sysu/imis/) to develop a community of practice aiming at creating a comprehensive system to link patient, provider and payer data specifically in context of health insurance systems. The initiative is jointly supported by the development agencies of the Swiss and the German government and works with partners from a number of organizations across Europe, Asia and Africa that are part of the openIMIS community (developers and implementers). The open source Insurance Management Information System (openIMIS) is an IT solution currently implemented by public and private not for profit health insurance schemes in Tanzania, Nepal, Cameroon, Congo and Chad, using mobile phones to administer the insurance scheme. At a global level, the initiative is working on making the solution more modular and interoperable so it can be used in different country contexts by insurers looking for low cost solutions to manage health insurance schemes.

Currently, parts of the openIMIS system are based on commercial software, which is in contrast to the overall objective of an open and free system that can be easily deployed worldwide without additional software licence fees. The commercial part includes the database back-end, which needs to be migrated from Microsoft SQL Server to PostgreSQL. This migration will be addressed by the Bachelor’s project and includes the following parts:

  1. Become familiar with the existing openIMIS implementation in general and the openIMIS database in particular.
  2. Create the openIMIS database structure in PostgreSQL: tables, views, user-defined types.
  3. Migrate Stored Procedures and Functions to PostgreSQL. Analyse and propose optimisation during the migration of Stored Procedures. This can also include the replacement of stored procedures by application-level code.
  4. Migrate in-line SQL scripts in all openIMIS related components: Web Application (legacy and modular), REST API, Web Services, Windows Services. Optional: construct SQL scripts using framework's ORM (Entity Framework or Django ORM) to become DBMS independent.
  5. Develop a tool for the transfer of data from the MSSQL database to the PostgreSQL database.
  6. Start the migration of SP from the database level to Python code in the modular backend using Django ORM (DBMS independent)

The work will take place in close collaboration between the SwissTPH and the Databases and Information Systems Research Group at the Department of Mathematics and Computer Science.

Start / End Dates

2020/04/06 - 2020/07/05

Supervisors

Research Topics