Avoid Duplicate Rows in Patient_State Table

[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.