Data Model
A decision was made to utilize the existing OSCAR workflows and UI elements as much as possible for CDX interoperability, so that users do not have to switch between different ‘tools’ to handle documents from different sources (e.g., CDX, Excelleris, etc.).
OSCAR has traditionally persisted documents as files in the file system and meta information about the documents in the database document table.
In order to minimize the changes to OSCAR, the CDX extension also uses the document table for storing meta-information about received documents. However, the actual CDX documents are persisted in the database and not in the file system. This is done in the new cdx_provenance and cdx_attachment table (see data model below). The cdx_provenance.payload attribute stores the entire XML of the received CDA document and each such document may have multiple attachments stored in the cdx_attachment table. In addition to storing received documents, the cdx_provenance table stores sent documents, as well.
Table cdx_pending_docs is used to record information about documents that either OSCAR attempted to download unsuccessfully, or that were manually deleted by an OSCAR user.
Database model
The CDX provider registry allows providers to work for multiple clinics. Therefore, there is a many-to-many relationship between providers (stored in the preexisting professionalSpecialists table and the clinics they work for, which are stored in the new cdx_clinics table. That relationship is represented in the new intersection table cdx_ClinicAndProfessionalIds.
Elements Catalogue
document
Pre-existing in OSCAR. Store meta information about uploaded documents. Now also used to store meta-data for received CDX documents.
patientLabRouting
Pre-existing in OSCAR. Used to associated received documents (and labs) with patients. Note: the lab_no foreign key conditionally points to either a record on table document or a record in table HL7TextInfo, depending on the value of attribute doc_type.
providerLabRouting
Pre-existing in OSCAR. Used to associated received documents (and labs) with providers. Note: the lab_no foreign key conditionally points to either a record on table document or a record in table HL7TextInfo, depending on the value of attribute doc_type.
demographic
Pre-existing in OSCAR. Used to maintain data on patient demographics.
provider
Pre-existing in OSCAR. Used to maintain data on providers.
ctl_document
Pre-existing in OSCAR. Not well document. Appears to maintain information on document status. module_id is conditional foreign key to demographic table, if module has value ‘demographic’ and module_id is not ‘-1’.
HL7TextInfo
Pre-existing in OSCAR. Used to maintain information on HL7 labs. (currently not used for CDX documents)
cdx_provenance
Newly added. Used to store information for received or sent CDX documents.
| Attribute | Description |
|---|---|
| id | serial id (PK) |
| doc_id | CDA document ID |
| version | CDA document version |
| effective_time | time document was created |
| parent_doc | parent of CDA document (if applicable). Foreign key to doc_id |
| set_id | id of document set (if applicable) |
| in_fulfillment_of_id | set if document is sent in fulfillment of another document or workflow (such as an order). Referral updates use this field to refer to the referral order document. Referral order documents use this field to refer to the internal referral ID. |
| kind | stores kind of document (LOINC display name) |
| action | either ‘import’ for imported documents or ‘SEND’ for sent documents |
| payload | XML content of the CDA document |
| document_no | foreign key to ‘document’ table (for imported documents) |
| msg_id | CDX message ID |
| warnings | warnings generated by the import engine about inconsistencies between EMR data and document data |
| status | status of document (e.g., ‘completed’, ‘corrected’, etc.) |
| distribution_status | this field is only populated for sent documents. It contains the distribution status (e.g., queued, delivered, etc) |
cdx_attachment
Newly added. Used to persist attachments for CDA documents.
| Attribute | Description |
|---|---|
| id | serial id (PK) |
| doc_id | foreign key to corresponding id in table cdx_provenane |
| attachment_type | type of attachment |
| content | payload of attachment |
| reference | name of attachment (if available) |
cdx_clinics
Newly added. Used to persist information about CDX enabled clinics.
| Attribute | Description |
|---|---|
| id | serial id (PK) |
| clinicId | CDX id for clinic |
| clinicName | name of clinic |
| clinicAddress | address of clinic |
cdx_ClinicAndProfessionalIds
Newly added. Represents N:N relationship between CDX enabled specialists and the clinics they work for.