Database Diagrams

The relationships of the tables which contain data is simple. Ideally, reports can be run against a single table.

Primary Data Tables

The two primary data tables are Requests and LabResults and their join is simple. Each RequestID can have multiple "Panels" assigned to it and each record in the Requests table represents each Panel. Each Panel can then have multiple LabResults associated with it in the LabResults table. The OBXSetId uniquely identifies the LabResult record associated with the Request/Panel.

Facilities and HealthcareAreas

The relationship for these table is discussed more on it's own page at:

https://sites.google.com/site/openldr/design-documents/facilities-and-healthcareareas

Table Relationships

Many of the tables have not been utilized by any implementations as of May 2018: The HL7 tables, Analyzers, MDR codes, Surveillance and while the Monitoring table is being populated there is not currently any reporting done to utilize the data; more explanation of this table is forthcoming.

See below for more explanation. Click on the image for an expanded view.

Not all of the tables are used in each implementation. In actuallvery few of the dictionary tables are currently used at all because the codes themselves are explanatory enough for reporting purposes.

The relationship from LabResults to Monitoring will soon have it's own page explaining the relationship and the contents of the Monitoring table.

The Facilities to HealthcareAreas join is done using a concatenation of the geographic codes for the level your are seeking information. There is more information about this on a separate page.

There will soon be a Laboratories table which will be joined to from the Requests table where the TestingFacilityCode and ReceivingFacilityCode will be changed to TestingLabCode and ReceivingLabCode which will be use to join to the new Laboratories table. This will allow for the more explicit identification of laboratories. You will be able to join from Laboratories to Facilities to get the geographic information about each Laboratory.