The Facilities and HealthcareAreas tables are designed to supplement information about facilities and classify those areas for reporting needs. The two table system was designed to reduce the number of tables and this page page describes the intended use of these tables.
Facilities
The term "facilities" is a generic term intended to cover locations that need to be tracked. For example a hospital will have its own FacilityCode. A laboratory within a hospital will often use the same facility code but in some cases you may need to distinguish between the two locations.
Ideally all the FacilityCodes would be official facility codes from a country's master facility list. This isn't always possible so there is the ability to map the facility codes used within the LIMS to the official facility code for the country.
FacilityCode
Description
FacilityType
MoHFacilityCode
CountryCode
ProvinceCode
RegionCode
DistrictCode
SubDistrictCode
LattLong
The FacilityCode is what will be used internally by data providers. Ideally they would be using the official MOH Facility Code; if they are, the FacilityCode column with match the MOHFacilityCode column.
The Description is essentially the name of the facility but can contain other useful information as needed.
The geographic codes are two digit codes representing the geographic locations of the facility within the country. Each country has a different hierarchy and nomenclature for these areas so these column names are meant to be generic. For example, a country might only organize things by Province and District and you can then just leave RegionCode empty.
When assigning the codes its important to remember that some codes might not be unique for the entire country. For example, say a country has 10 Provinces with each Province having 10 Districts. The may have coded Districts within each Province to restart the number scheme over at 1 rather than have 100 unique District codes. You might have Province 'XX' with a District '01' but you might also have Province 'AA' and a District '01'. This comes into play when joining to the HealthcareAreas table to get more information on those Districts.
HealthcareAreas
The HealthcareAreas table is a lookup table to find more information about the geographic levels within the country. These are often how the reporting data is aggregated. This table will contain individual rows for each geographic level. Each province will have its own row as will each District. The HealthcareAreaCode will be a combination of those two digit codes mentioned in the Facilities table. The row for a Country would only have a two digit HealthcareAreaCode (e.g. MZ, TZ, ZM). If Province was the next hierarchical geographic level for the country the HealthcareAreaCode would be 4 digits (MZXX, MZAA). If Districts are below Provinces they would have 6 digit HealthcareAreaCode (MZXX01, MZYY01).
Utilizing the Code columns in the Facilities table you can concatenate the values together to form the code for the level you need and then use that to join to the HealthcareAreas table. The following view demonstrates how to do this. The sections of the view are explained below.
SELECT facility.*,
countryInfo.HealthcareAreaDesc AS CountryName, countryInfo.LattLong AS CountryLattLong,
provinceInfo.HealthcareAreaDesc AS ProvinceName, provinceInfo.LattLong AS ProvinceLattLong,
regionInfo.HealthcareAreaDesc AS RegionName, regionInfo.LattLong AS regionLattLong,
districtInfo.HealthcareAreaDesc AS DistrictName, districtInfo.LattLong AS DistrictLattLong,
subDistrictInfo.HealthcareAreaDesc AS SubDistrictName, subDistrictInfo.LattLong AS SubDistrictLattLong
FROM
(
SELECT *,
CountryCode AS HealthcareCountryCode, -- Not strictly necessary but makes it consistent
CONCAT(CountryCode, ProvinceCode) AS HealthcareProvinceCode,
CONCAT(CountryCode, ProvinceCode, RegionCode) AS HealthcareRegionCode,
CONCAT(CountryCode, ProvinceCode, RegionCode, DistrictCode) AS HealthcareDistrictCode,
CONCAT(CountryCode, ProvinceCode, RegionCode, DistrictCode, SubDistrictCode) AS HealthcareSubDistrictCode
FROM Facilities
) AS facility
LEFT JOIN HealthcareAreas AS countryInfo ON facility.HealthcareCountryCode = countryInfo.HealthcareAreaCode
LEFT JOIN HealthcareAreas AS provinceInfo ON facility.HealthcareProvinceCode = provinceInfo.HealthcareAreaCode
LEFT JOIN HealthcareAreas AS regionInfo ON facility.HealthcareProvinceCode = regionInfo.HealthcareAreaCode
LEFT JOIN HealthcareAreas AS districtInfo ON facility.HealthcareDistrictCode = districtInfo.HealthcareAreaCode
LEFT JOIN HealthcareAreas AS subDistrictInfo ON facility.HealthcareSubDistrictCode = subDistrictInfo.HealthcareAreaCode;
The yellow highlighted inner select is where the code columns are concatenated to create the equivalent HealthcareAreaCode columns. Those codes are then used to join over to the HealthcareAreas table.
Notice that each derived column is used as a separate join to find the relevant data; e.g. you use the HealthcareDistrictCode to join to HealthcareAreas to find all of the Districts.
In the blue highlighted SELECT clause notice the HealthcareDescription need clarified for each geographic level.
Views and Selects
It is suggested you create a view in your OpenLDRDict using similar SQL to the example above. You may need to make changes depending you which geographic levels you use and the order they need to be in to form the hierarchical level. Then it's very easy to get the ProvinceName and DistrictName for the FacilityCode columns within the Request table.
SELECT req.RequestingFacilityCode,
requestFacility.ProvinceName AS ReqestingFacilityProvinceName, requestFacility.DistrictName AS ReqestingFacilityDistrictName,
testingFacility.ProvinceName AS TestingFacilityProvinceName, testingFacility.DistrictName AS TestingFacilityDistrictName
FROM Requests AS req
LEFT JOIN OpenLDRDict.dbo.viewFacilities AS requestFacility ON req.RequestingFacilityCode = requestFacility.FacilityCode
LEFT JOIN OpenLDRDict.dbo.viewFacilities AS testingFacility ON req.TestingFacilityCode = testingFacility.FacilityCode