Describe the feature you'd like and what it will do
Replace existing strict many-to-many relationship between observations and result qualifiers with an array field on the observation table containing result qualifier codes.
The result qualifier table becomes a vocabulary table that defines supported codes and definitions. Codes can be scoped to either the entire instance or to specific workspaces. Codes must be unique within a workspace and at the instance level. Duplicate codes between different workspaces will be allowed.
Observation endpoints will return a dictionary of result qualifier codes and definitions for responses whose observations include result qualifier codes.
Changing the value of a result qualifier code will not automatically change the corresponding codes attached to observations. A separate automated or manual process will need to be created to update existing codes.
Consider adding a GIN index on the result_qualifiers field, if filtering or updates on result qualifiers is necessary.
Why is this feature important?
The current many-to-many constraint between observations and result qualifiers ensures data integrity, but adds substantial overhead to reads and writes to the observation table that use result qualifiers. An array field of codes directly on the observation table sacrifices some of this data integrity, but reduces overhead on observations which will help speed up data operations used by auto QC monitoring and the QC app.
Is your feature request related to a problem? Please describe.
No response
Any additional comments?
An earlier iteration of the observation table used an array field as well, however we were storing result qualifier IDs in that field, not codes. Codes are generally significantly smaller than the UUIDs, and require fewer additional queries by the web server to build observations responses since the codes can be passed directly to the response without transforming them from IDs to codes.
Another benefit of this approach is that it would allow partitioning of the observations table again, if needed.
Describe the feature you'd like and what it will do
Replace existing strict many-to-many relationship between observations and result qualifiers with an array field on the observation table containing result qualifier codes.
The result qualifier table becomes a vocabulary table that defines supported codes and definitions. Codes can be scoped to either the entire instance or to specific workspaces. Codes must be unique within a workspace and at the instance level. Duplicate codes between different workspaces will be allowed.
Observation endpoints will return a dictionary of result qualifier codes and definitions for responses whose observations include result qualifier codes.
Changing the value of a result qualifier code will not automatically change the corresponding codes attached to observations. A separate automated or manual process will need to be created to update existing codes.
Consider adding a GIN index on the result_qualifiers field, if filtering or updates on result qualifiers is necessary.
Why is this feature important?
The current many-to-many constraint between observations and result qualifiers ensures data integrity, but adds substantial overhead to reads and writes to the observation table that use result qualifiers. An array field of codes directly on the observation table sacrifices some of this data integrity, but reduces overhead on observations which will help speed up data operations used by auto QC monitoring and the QC app.
Is your feature request related to a problem? Please describe.
No response
Any additional comments?
An earlier iteration of the observation table used an array field as well, however we were storing result qualifier IDs in that field, not codes. Codes are generally significantly smaller than the UUIDs, and require fewer additional queries by the web server to build observations responses since the codes can be passed directly to the response without transforming them from IDs to codes.
Another benefit of this approach is that it would allow partitioning of the observations table again, if needed.