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:

Creating two new tables, an attribute table, and an attribute type table.
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
facilityid	reference to facility that has the custom attribute
facilityattributetypeid	reference to attribute key (rather than just a text field describing the type)
value	attribute value

facility_attribute_types

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

Example data

facility_attributes

id	facilityid	facilityattributetypeid	value
1	1	1	blue
2	2	1	red
3	1	2	100
4	2	2	200

facility_attribute_types

id	name	description	format
1	color	Facility "color"	select
2	max_occupancy	Facility maximum occupancy	number

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

id

code

name

attributes

1

F01

Example Hospital

{

"color": "blue",

"max_occupancy": 100

}

2

F02

Example 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.