# Database Synchronization Using Reverse Query Method: The Case of SIMRS Khanza

### Database Synchronization Using Reverse Query Method: The Case of SIMRS Khanza

In the realm of hospital information system development, like the Hospital Management Information System (SIMRS) Khanza, database synchronization is a common challenge. This process is crucial to ensure that the stored data is always up-to-date and consistent across the system. One method that can be employed to achieve this goal is the reverse query method.

#### What Is the Reverse Query Method?

The reverse query method refers to the process of reversing the direction of information search. In the context of database synchronization, this means starting from the desired outcome - a synchronized and updated database - and working backwards to determine the necessary steps to achieve it.

#### Case Example in SIMRS Khanza

Let's consider a scenario where the `deposit` and `ipsrsopname` tables in the SIMRS Khanza database need to be synchronized to update the structure and remove data duplications.

##### Step 1: Preparing the `deposit` Table

1. **Remove Old Primary Key**:
    
    ```sql
    ALTER TABLE `deposit` DROP PRIMARY KEY;
    ```
    
    This step is necessary for changing the primary key structure, possibly due to business requirements change or database design improvement.
    
2. **Update the** `no_deposit` Column:
    
    ```sql
    UPDATE `deposit` SET no_deposit = LEFT(no_deposit, 17);
    ```
    
    This command modifies the `no_deposit` values to ensure uniform formatting, which might be crucial for system integration or data standardization.
    
3. **Identify and Remove Duplications**:
    
    ```sql
    DELETE c1 FROM deposit c1 INNER JOIN deposit c2 WHERE c1.`tgl_deposit` < c2.`tgl_deposit` AND c1.`no_deposit` = c2.`no_deposit`;
    ```
    
    This query removes duplicate entries based on `no_deposit`, retaining only the most recent based on `tgl_deposit`.
    
4. **Modify and Re-add Primary Key**:
    
    ```sql
    ALTER TABLE `deposit` MODIFY COLUMN `no_deposit` varchar(17) NOT NULL FIRST;
    ALTER TABLE `deposit` ADD PRIMARY KEY(`no_deposit`);
    ```
    
    These steps change the `no_deposit` column to the appropriate data type and set it as the primary key, ensuring data integrity.
    

##### Step 2: Preparing the `ipsrsopname` Table

1. **Add Temporary** `id` Column:
    
    ```sql
    ALTER TABLE `ipsrsopname` ADD `id` INT AUTO_INCREMENT UNIQUE;
    ```
    
    This adds an `id` column to facilitate unique identification of each row.
    
2. **Remove Duplications**:
    
    ```sql
    DELETE FROM `ipsrsopname` WHERE id NOT IN ( SELECT MAX(id) FROM `ipsrsopname` GROUP BY `kode_brng`, `tanggal` );
    ```
    
    This query removes duplicate rows based on the combination of `kode_brng` and `tanggal`, leaving only the most recent.
    
3. **Clean Up and Reset Primary Key**:
    
    ```sql
    ALTER TABLE `ipsrsopname` DROP `id`;
    ALTER TABLE `ipsrsopname` ADD PRIMARY KEY (`kode_brng`, `tanggal`) USING BTREE;
    ```
    
    These steps remove the temporary `id` column and set a new primary key combination of `kode_brng` and `tanggal` using B-Tree data structure for query efficiency.
    

##### Step 3: Next, perform this on all tables as per their respective issues.

#### Conclusion

The reverse query method in database synchronization allows administrators and developers to strategically plan and implement database structure updates and data cleaning. Through the example on SIMRS Khanza, we can see how this approach facilitates data structure enhancement and redundancy elimination, leading to a more efficient system and more accurate data. The steps, though technical, demonstrate how the concept of reverse query can be applied in real practice to address database synchronization challenges.