2021 – Complex Structure Changes 1 – RDBMS

Its time to deal with the inevitable complex changes to the database. I’ll start working through the RDBMS changes  with this post that handles the following:

  • Non-Unique Patient Names
    • Initially the patient management system was used to maintain Ordering System (OS) and Medical Record Number (MRN) combinations for unique patients (by name). But patient management has proven to not be that simple so I cant rely on patient names being unique.
  • EMPI
    • Enterprise Master Patient Indexes will be used to group applicable patient records together.

But first here is a recap of the existing tables.

CREATE TABLE patient
( patient_id   NUMBER       NOT NULL,
  patient_name VARCHAR2(30) NOT NULL);

ALTER TABLE patient
ADD CONSTRAINT patient_pk
PRIMARY KEY ( patient_id );

ALTER TABLE patient
ADD CONSTRAINT patient_uk
UNIQUE ( patient_name );

And next is the patient MRN table.

CREATE TABLE patient_mrn
( ordering_system NUMBER       NOT NULL,
  patient_id      NUMBER       NOT NULL,
  patient_mrn     VARCHAR2(30) NOT NULL,
CONSTRAINT mrn_to_patient
FOREIGN KEY ( patient_id )
REFERENCES patient);

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

ALTER TABLE patient_mrn
MODIFY ( start_date NOT NULL );

And a sequence for patient ID values.

CREATE SEQUENCE patient_seq START WITH 10;

Patient Names Are Not Unique

Consider this data that creates two patient records. Each with 1 or more Ordering System (OS) and Medical Record Number (MRN) values.

INSERT INTO patient ( patient_id, patient_name )
    VALUES(patient_seq.nextval,'Rosco Coltrane');
INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn )
    VALUES(patient_seq.currval,1,'MRN123');
INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn )
    VALUES(patient_seq.currval,2,'MRN456');

INSERT INTO patient ( patient_id, patient_name )
    VALUES(patient_seq.nextval,'Enos Strate');
INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn )
    VALUES(patient_seq.currval,1,'MRN88');

COMMIT;

The original belief was no patient would ever have more than one MRN in a single OS. But that is not the case. There can be more than one patient record for a single human and multiple MRN’s per OS are allowed. The current database does not allow this as shown in these statements.

INSERT INTO patient ( patient_id, patient_name )
     VALUES(patient_seq.nextval,'Rosco Coltrane');
--Raises ORA-00001: unique constraint (ADMIN.PATIENT_UK) violated--
INSERT INTO patient_mrn ( patient_id, ordering_system, patient_mrn )
    VALUES(patient_seq.currval,1,'MRN99');

So I need to remove the unique constraint but keep the index on patient name to expedite queries.

ALTER TABLE patient DROP CONSTRAINT patient_uk;
CREATE INDEX name_ix ON patient ( patient_name );

Now the second Rosco is welcomed into the database with open arms.

Enterprise Master Patient Index (EMPI)

Tracking which are for the same human is done with EMPI number that link one or more patient records to a single unique EMPI number. Here’s the DDL to create the EMPI table.

CREATE TABLE empi
( empi_number NUMBER NOT NULL );

ALTER TABLE empi
ADD CONSTRAINT empi_pk
PRIMARY KEY ( empi_number );

And the table to map patient records to the EMPI number.

CREATE TABLE empi_patient_map
( empi_number NUMBER NOT NULL,
  patient_id NUMBER NOT NULL,
  obsolete   NUMBER NOT NULL );

ALTER TABLE empi_patient_map
ADD CONSTRAINT empi_to_empi
FOREIGN KEY (empi_number)
REFERENCES empi;

ALTER TABLE empi_patient_map
ADD CONSTRAINT empi_to_patient
FOREIGN KEY (patient_id)
REFERENCES patient;

ALTER TABLE empi_patient_map
ADD CONSTRAINT empi_map_pk
PRIMARY KEY ( empi_number, patient_id, obsolete );

And a sequence to generate EMPI numbers.

CREATE SEQUENCE empi_seq;

Populating EMPI

The EMPI and mapping table can be populated with a SQL to insert into multiple tables like this.

INSERT ALL
  INTO empi ( empi_number )
VALUES ( empi_seq.nextval )
  INTO empi_patient_map ( empi_number,
                          patient_id,
                          obsolete )
VALUES( empi_seq.currval,
        patient_id,
        obsolete)
SELECT patient_id,
       0 obsolete
  FROM patient;

And query the results.

SELECT empi.empi_number empi,
        patient_name,
        ordering_system os,
        patient_mrn     mrn
  FROM empi,
       empi_patient_map epm,
       patient,
       patient_mrn pm
  WHERE empi.empi_number = epm.empi_number
    AND epm.patient_id   = patient.patient_id
    AND pm.patient_id    = patient.patient_id;

Here’s the result.

EMPI PATIENT_NAME    OS  MRN
---- --------------- --- -------
   1 Rosco Coltrane    1 MRN123
   1 Rosco Coltrane    2 MRN456
   2 Enos Strate       1 MRN88
   3 Rosco Coltrane    1 MRN99

I assigned 1 EMPI number to each patient record to seed the database. Later on I’ll build a PL/SQL function merge them as required. For now I’m happy to have the new tables in place.

Thanks for reading!

Leave a Comment