OpenLMIS v3 Architecture ExtraData approaches

Currently there are two main approaches for implementing ExtraData (custom attributes) for core domain models/tables. Each approach would be applied to each model/table that will support extra data. They are:

  1. Creating two new tables, an attribute table, and an attribute type table.
  2. Adding a column of type json (or a column of type text that will have valid json).

Attribute tables

In this approach, two new tables would be created for each model. For example, if the facilities table needs custom attributes, the two new tables would be facility_attributes and facility_attribute_types.

Potential schema

facility_attributes

id 
facilityidreference to facility that has the custom attribute
facilityattributetypeidreference to attribute key (rather than just a text field describing the type)
valueattribute value

facility_attribute_types

id 
namename of attribute "key"
description(optional) description of attribute
format(optional) key type, for possible help in display

Example data

facility_attributes

idfacilityidfacilityattributetypeidvalue
111blue
221red
312100
422200

facility_attribute_types

idnamedescriptionformat
1colorFacility "color"select
2max_occupancyFacility maximum occupancynumber

Note: format would represent the recommended way to display possible values for the type, e.g. select for a selection from a list. For this example, the list to choose from would be in another table.

Findings

A small spike was done to see the viability of implementing this in our proposed Spring Boot / Data REST framework and it does seem possible to do. Java Classes would need to be defined for each table (FacilityAttribute, FacilityAttributeType) with references to each other. In order to do creates and updates on the attributes, their types and all of the associations, separate repositories would need to be defined as well (FacilityAttributeRepository, FacilityAttributeTypeRepository). If exposing these new repositories is not desired, a custom controller could be defined to give a specific interface to creating and updating the model's custom attributes.

JSON column

In this approach, each model would have a new field of json type. Custom attributes would be represented in JSON in the field.

Example data

facilities

idcodenameattributes
1F01Example Hospital

{

"color": "blue",

"max_occupancy": 100

}

2F02Example Clinic

{

"color": "red"

}

Findings

A small spike was done to determine viability with Spring Boot / Data REST. It is not clear how to take the JSON in the custom field and simply "pass it through" to the API JSON response. Jackson in Spring takes POJOs and serializes it to JSON, but that is not the same thing as "converting" from JSON to JSON. It is possible to serialize/deserialize JSON from a CRUD call and save it to the JSON column, but it requires having a Java class with members to define a "schema" for the JSON, as the JSON gets converted to a POJO before getting converted back to JSON. This double conversion also makes the JSON output somewhat different from the input, as some attributes become defined and their values are null.

OpenLMIS: the global initiative for powerful LMIS software