Uploaded image for project: 'OpenMRS Core'
  1. OpenMRS Core
  2. TRUNK-2273

Avoid Duplicate Rows in Patient_State Table

    Details

    • Complexity:
      Low

      Description

      [Note: we're not going to do the solution described in the description. See the "Proposed Solution" comment".]

      While running queries on the DB, I found that the UI gave me different results than the database when trying to determine the start_date of the current treatment status of a patient (the latest end_date or end_state = null)

      As it turns out, the patient had duplicate rows on this key(patient_program_id, end_date, voided).

      Working under the assumption that for any patient program id, there should only be one status with a particular start date (no multiple concurrent statuses) and that a start_date should always be valued,

      the patient_state table needs to have 2 unique keys to prevent bogus data and also not allow the start_date to be null:

      – alters
      alter table patient_state modify start_date date not null;
      alter table patient_state add unique key(patient_program_id, end_date, voided);
      alter table patient_state add unique key(patient_program_id, start_date, voided);

      – creates
      CREATE TABLE `patient_state` (
      `start_date` date NOT NULL,
      UNIQUE KEY `patient_program_id_start_date` (`patient_program_id`,`start_date`,`voided`),
      UNIQUE KEY `patient_program_id_end_date` (`patient_program_id`,`end_date`,`voided`),
      );

      Porting will be a problem as most installs will probably violate these constraints. Suggest that corrections will need to be made manually prior to a port, instead of adding the constraint and automatically deleting duplicates of the constraints. Unfortunately, I don't know of a way to add a unique constraint without checking, as is possible in MS SQL Server.

      Here are the queries to identify the problem:
      – duplicate voided end dates
      select patient_program_id, patient_state_id, count(end_date) as sed
      from patient_state
      where voided=1
      group by patient_program_id, end_date
      having count(end_date) > 1;

      – duplicate voided start_dates
      select patient_program_id, patient_state_id, count(start_date) as sed
      from patient_state
      where voided=1
      group by patient_program_id, start_date
      having count(start_date) > 1;

      – duplicate non-voided end dates
      select patient_program_id, patient_state_id, count(end_date) as sed
      from patient_state
      where voided=0
      group by patient_program_id, end_date
      having count(end_date) > 1;

      – duplicate non-voided start_dates
      select patient_program_id, patient_state_id, count(start_date) as sed
      from patient_state
      where voided=0
      group by patient_program_id, start_date
      having count(start_date) > 1;

      – null start_dates
      select patient_program_id, patient_state_id from patient_state where start_date is null group by patient_program_id;

      As a side-effect to this, the user won't be able to void a value that is identical to one already voided.

      Alternatively, the UI code should be modified to check these constraints before allowing the user to persist such data. This may be a much easier solution then above due to the need for database cleanup.

        Gliffy Diagrams

          Attachments

            Attachments-Category-Modification

              Activity

                People

                • Assignee:
                  wyclif Wyclif Luyima
                  Reporter:
                  achi@pih.org Andrew Chi
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: