Database API

This document describes the database structure which lies under DHIS 2. Note that the application itself is database independent. It is recommended to interact with the database through the DHIS 2 Java API.

Table of contents

• Name and type conventions
• Best practices
• Alphabetic list of tables
• Core tables
• Organisation units
• Data sets
• Validation
• Indicators
• Aggregation

Name and type conventions

• All table and column names shall be in lower case.
• When organisationunit is suffixed by something (except for id), the abbreviation shall be orgunit used. Example: orgunitgroupset.
• Each table shall have an id prefixed with the table name. Example: datasetid.
• Each foreign key shall have the exact name of the field it refers to. Example: datavalue.dataelementid > dataelement.dataelementid.
• No other field shall use the table name as a prefix. This is inferred from the table name and the id.
• Name fields shall have a length of 160 characters.
• Description fields shall have a length of 360 characters.
• Each short name and code shall have a length of 25 characters.
• Each type field shall contain a word rather than an an integer constant. It's length shall be 16. This is because the constant is only known to Java. Example: dataelement.aggregationoperator. In the Java code 1 is "sum" and 2 is "average". In the database, the actual names "sum" and "average" should appear.

Best practices

Do not use SELECT * FROM table;

If you do, you should at least make sure it can handle any future columns being added to the table without breaking your code.
Specify all column names that you need properly:

SELECT table.columnone, table.columntwo FROM table;

All references to column names should be qualified by an alias or the full table name, ESPECIALLY IN JOINS:

SELECT users.surname, users.firstname, usersettings.name FROM users, usersettings;

OR something like this with aliases:

SELECT u.surname, u.firstname, us.name FROM users u, usersettings us;

But NOT:

SELECT surname, firstname, name FROM users, usersettings;

Alphabetic list of tables

• aggregateddatavalue
• aggregatedindicatorvalue
• dataelement
• dataelementgroup
• dataelementgroupmembers
• dataset
• datasetmembers
• datasetsource
• datavalue
• frequencyoverrideassociation
• indicator
• indicatorgroup
• indicatorgroupmembers
• indicatortype
• minmaxdataelement
• organisationunit
• orgunitgroup
• orgunitgroupmembers
• orgunitgroupset
• orgunitgroupsetmembers
• orgunitgroupsetstructure
• orgunithierarchy
• orgunitstructure
• period
• periodtype
• source
• structure
• userinfo
• userrole
• userroleauthorities
• userroledataset
• userrolegroupmembers
• users
• usersetting

Core tables

dataelement

Data elements are the defitiontions of what users can register data for. For each data element a set of data values can be registered. For example "Number of polio vaccinations" or "Total population in district".

dataelementgroup

A group represents a logical grouping of data elements, for example all data elements related to births .

dataelementgroupmembers

Which data elements are connected to which groups.

period

Periods define a time period between a start and end date, for example 1. of January to 31. of January. Each period is connected to a period type, defining what sort of period this is.

periodtype

Period types are for example "monthly", "quarterly", "yearly", etc. The rows in this table mirror specific Java classes, MonthlyPeriodType, YearlyPeriodType, etc. Do not add, delete or change rows in this table unless you also alter the PeriodType definitions and bean mappings in the system.

source

A source represents something that can store a data value. The clearest example is an organisation unit, which is described in later tables. Other subtypes of this table may be "patient", "bus", "facility", "house" or anything else that data might apply to. This table thus represents an abstraction over it's subtypes and doesn't lock DHIS 2 down to strictly using organisation units.

datavalue

Datavalues are the actual values registered in the system. Each value is registered for a specific data set, a period and a source. An example is: The number of polio vaccinations administered for Clinic A in March 2006 was 10, where 10 is the actual datavalue. Each value is stored with meta information on who registered it, when it was last updated and an optional comment.

Organisation units

organisationunit

An organisation unit is a type of source. Organisation units are ordered in a hierarchy through a parent property. An organisation unit may specify the date when it was opened and when it was closed.

orgunitgroup

WAS: organisationunitgroup

Organisation unit groups represent logical groupings of organisation units. Example include all organisation units near a water source, ...

orgunitgroupmembers

WAS: organisationunitgroupmembers
NEW NAME: orgunitgroupmembers

Which organisation units are members of which organisation unit groups.

orgunitgroupset

WAS: organisationunitgroupset

orgunitgroupsetmembers

WAS: groupsetmembers

Which organisation unit groups are members of which organisation unit group sets.

Data sets

dataset

Datasets are collections of data elements one captures data for in the same session, usually the same as paper forms. It is presented as one screen in the Data Entry module in the application. Each dataset has a reporting frequency, identified by the reference to a period type. This frequency tells you how often the sources using these datasets are supposed to register and report data upward in the hierarchy,

datasetmembers

Which data elements are connected to which datasets.

datasetsource

WAS: dataregisteringassociation

Which sources register data for which datasets. Datasets can be created independently of who are supposed to register data for them. This association allows users to add a connection between a source, for example an organisation unit and a dataset, saying that this source is expected to report data for this dataset.

frequencyoverrideassociation

NEW NAME: ???

Which registering frequencies for which datasets that apply to different sources. The dataset defines a basic registering frequency, for example monthly, meaning how often the source is supposed to register and report data upwards in the hiearchy. This table allows sources to override how often they register data for a given dataset. It is expected that the new registering frequency is always shorter than the original frequency. For example, if Dataset A has a frequency of monthly, Source B can choose to register data for it on a weekly basis. Data would still be reported upward on a monthly basis, but Source B can choose to make use of more detailed data in it's operation.

Validation

minmaxdataelement

This table stores the minimum and maximum values allowed for data values that are registered for a certain data element in a particular organisation unit. The numbers are often a result of looking at long term trends for the data element and is used to prevent registering erroneously high or low values. Sometimes a value will in fact break the min and max constraints, and in such case the comment field of the data value is typically used to explain the deviation.

Indicators

indicator

Where data elements represent one type of data, indicators show relationships between different data elements. For example, based on the data elements "Number of people vaccinated for " and "Total population in district", one can create an indicator called " vaccination coverage for district." In this example, the denominator formula would be the id of the population data element while the numerator forumula would be the id of the data element for number of vaccinated people. The aggregation operator for the denominator would be average, averaging different population counts over a period of time, while the operator for the numerator would be sum, summing together the different number of vaccinations over the same period of time. Formulas can be complex, using mathemathical operators between the parts, for example "de1 + de 2".

indicatortype

The indicator type defines how the end result of an indicator calculation (a number between 0 and 1) shall be presented using the factor property. An example is "Percentage", where the factor would be 100. An indicator value of 0.5 would then become 50%.

indicatorgroup

Indicator groups represent logical grouping of indicators, for example .

indicatorgroupmembers

Which indicators are members of which indicator groups.

Aggregation

aggregateddatavalue

This table holds all aggregated data values when they have been exported to the datamart. It's used to speed up the use of aggregated values.

aggregatedindicatorvalue

This table holds all aggregated values for indicators when they have been exported to datamart. It's used to speed up the use of aggregated values.

orgunithierarchy

WAS: organisationhierarchy

Organisationunit hierarchies represent snapshots of how the organisation units in the system are organised at any given time. This table should be updated everytime an organisation unit is added, updated , moved or deleted.

structure

This table holds the actual structure refered to by the orgunithierarchy.

orgunitstructure

WAS: organisationunitstructure

This table represents a "flattening" of the organisation unit hierarchy. Each graph from a unit to it's parent is stored as a row in this table. It can be used for pivot tables.

(Questions on wether this table could be made dynamic (generated every time) instead of locked to seven levels.)

orgunitgroupsetstructure

WAS: groupsetstructure

This table "flattens" the relationships netweem groups, org units and group sets. Can be used in pivot tables.

Users and authorities

userinfo

WAS: users

This table holds general information about users, like their names and addresses. A user may be connected to a specific organisation unit. This connection may used to specify the user's authority priveliges.

users

WAS: usercredentials

This table holds login information for users.

userrole

WAS: userauthoritygroup

userrolemembers

WAS: userauthoritygroupmembers

Which users are members of which user roles.

userroleauthorities

WAS: userauthoritygroupauthorities

userroledataset

WAS: userauthoritygroupdataset

usersetting

Configuration settings and options for different users.