2021 – RDBMS Structure Changes With Database Unavailable

The patient management system has been given some new requirements.

  1. OS/MRN combinations must have a start date
  2. The start date defaults to the current date
  3. OS/MRN combinations may have and end date

And there is no requirement for the database to be available while the changes are made! That makes things really simple…

Step 1 – Add New Columns

The start and end date values will be recorded for each OS/MRN combination so new columns are required in the PATIENT_MRN table.

ALTER TABLE patient_mrn
ADD ( start_date DATE DEFAULT SYSDATE,
      end_date   DATE );

Step 2 – Set New Column Values

The start date values default to the current date so set them now.

UPDATE patient_mrn
SET start_date = SYSDATE;

Step 3 – Set NULL

Ensure that future rows include a start date value by making the column non-nullable.

ALTER TABLE patient_mrn
MODIFY ( start_date NOT NULL );

Step 5 – Alter PLSQL API

At this point the PL/SQL API introduced here will not work because it is not aware of the new columns. Let’s handle that now.

REPLACE PROCEDURE add_patient ( p_name VARCHAR2,
                                p_os NUMBER,
                                p_mrn VARCHAR2,
                                p_sdate DATE DEFAULT SYSDATE,
                                p_edate DATE DEFAULT NULL) AS
BEGIN
  -- insert the patient record using the sequence
  -- generator for the patient id
  INSERT INTO patient(patient_id,
                      patient_name)
  VALUES(patient_seq.nextval,
         p_name);
  -- insert the os and mrn using the patient id
  -- from the sequence generator
  INSERT INTO patient_mrn(patient_id,
                          ordering_system,
                          patient_mrn,
                          start_date,
                          end_date)
  VALUES(patient_seq.currval,
         p_os,
         p_mrn,
         p_sdate,
         p_edate);
END;

And now test it out with 3 different cases:

  1. Values provided for all parameters
    1. Including the new start and end dates
  2. Values provided for all parameters except end date
    1. Including the start date while the end_date defaults to NULL
  3. Values provided for all parameters except start date and end date
    1. The start date takes on a default value of SYSDATE
    2. The end date takes on a default value of NULL

The situation tested by number 3 allows programs to the API if they don’t provide start and end dates.

BEGIN
  -- Test 1
  add_patient('All Values',1,1111111111,SYSDATE,SYSDATE + 10);
  -- Test 2
  add_patient('All Except EDate',1,2222222222,SYSDATE);
  -- Test 3
  add_patient('All Except Dates',1,3333333333);
END;

And the results.

SELECT patient_name,
       ordering_system OS,
       patient_mrn     MRN,
       start_date      sdate,
       end_date        edate
  FROM patient p,
       patient_mrn pmrn
 WHERE patient_name LIKE 'All%'
  AND p.patient_id = pmrn.patient_id;


PATIENT_NAME     OS MRN        SDATE     EDATE
---------------- -- ---------- --------- ---------
All Values       1  1111111111 25-JUL-21 04-AUG-21
All Except EDate 1  2222222222 25-JUL-21
All Except Dates 1  3333333333 25-JUL-21

A summary of the steps above is:

  • Add new columns (determines column name and datatype)
  • Set new column values as required
  • Set new columns for future behavior (NULLABLE, etc)
  • Update PLSQL API (allow for pre and post change calls)

In the next post we’ll compare this to the same new requirements in JSON.

Thanks for reading!

Leave a Comment