@Christian_Reich I implemented the non-standard table for procedure details and I am publishing it here so anyone can comment on it, use it or just tell me that it does not make sense at all.
The proposed solution combines elements of VISIT_OCCURRENCE <-> VISIT_DETAIL relationship with fields from MEASUREMENT and OBSERVATION tables.
Reason for Extension
The procedure occurrence record can contain only one modifier that can be implemented as concept only. Therefore it is not convenient to store more parameters of PROCEDURE_OCCURRENCE, as for example number of sonications, sonications power in case of MRI guided high intensity focused ultrasound ablation thalamotomy. Theoretically, they could be stored in the OBSERVATION table, but since their domain would be Procedure, this would require creating corresponding records in the PROCEDURE_OCCURENCE table. Moreover, still remains the problem with representing structures of the procedure parameters, eg. arrays of “sonication power”, aggregates as eg. “maximum power in series of sonications” or hierarchical.
Table Description
The PROCEDURE_DETAIL table is an optional table used to represent additional details and parameters of existing PROCEDURE_OCCURRENCE records. For every record in the PROCEDURE_OCCURRENCE table there may be 0 or more records in the PROCEDURE_DETAIL table with a 1:n relationship where n may be 0. The PROCEDURE_DETAIL table is composed of some parts of PROCEDURE_OCCURRENCE, MEASUREMENT and OBSERVATION tables and belongs to the procedure domain. Using parent_procedure_detail_id and preceding_procedure_detail_id allows us to compose flat or hierarchical structures of related parameters.
User Guide
The configuration defining the Procedure Detail is described by Concepts in the Procedure Domain, which form a hierarchical structure. The Procedure Detail record will have an associated to the Procedure Occurrence record in two ways:
- The Procedure Detail record will have the PROCEDURE_OCCURRENCE_ID it is associated with
- The PROCEDURE_DETAIL_CONCEPT_ID will be a descendant of the PROCEDURE_CONCEPT_ID for the Procedure.
Sequence of parameters for complex procedures can be represented with preceding_procedure_detail_id. For example, if there was performed FUS ablation thalatomy with 5 sonications, power of each sonication can be represented as Power of Sonication detail, where each record (except for the first one) will have id of the previous sonication power.
To represent hierarchical structure of records, please use parent_procedure_detail_id.
ETL Conventions
It is not mandatory that the PROCEDURE_DETAIL table be filled in, but if you find that the parameters of the performed procedure can have an impact on the cause-effect relationships in your statistical analysis, it is a good idea to use PROCEDURE_DETAIL. For example, the number of sonications in MRI guided high intensity focused ultrasound ablation thalamotomy on essential tremor condition degree expressed with FTM score.
| CDM Field |
User Guide |
ETL Conventions |
Datatype |
Required |
PK |
Foreign Key |
FK Table |
FK Domain |
| procedure_detail_id |
The unique key given to a procedure detail record for a person. |
Each instance of a procedure detail in the source data should be assigned this unique key. |
integer |
Yes |
Yes |
No |
|
|
| person_id |
The PERSON_ID of the PERSON for whom the procedure is recorded. This may be a system generated code. |
|
integer |
Yes |
No |
Yes |
PERSON |
|
| procedure_detail_concept_id |
This field is recommended for primary use in analyses, and must be used for network studies. This is the standard concept mapped from the source value which represents a procedure detail. |
The CONCEPT_ID that the PROCEDURE_SOURCE_VALUE maps to. Only records whose source values map to standard concepts with a domain of “Procedure” should go in this table. Accepted Concepts. |
integer |
Yes |
No |
Yes |
CONCEPT |
Procedure |
| procedure_detail_type_concept_id |
This field can be used to determine the provenance of the Procedure record, as in whether the procedure was from an EHR system, insurance claim, registry, or other sources. |
Choose the concept id that best represents the provenance of the record, for example whether it came from an EHR record or billing claim. |
integer |
Yes |
No |
Yes |
CONCEPT |
Type Concept |
| provider_id |
The provider associated with the procedure record, e.g. the provider who performed the Procedure. |
The ETL may need to make a choice as to which PROVIDER_ID to put here. |
integer |
No |
No |
Yes |
PROVIDER |
|
| procedure_detail_source_value |
This field houses the verbatim value from the source data representing the procedure detail. For example, this could be an CPT4 or OPCS4 code. |
Use this value to look up the source concept id and then map the source concept id to a standard concept id. |
varchar(50) |
No |
No |
No |
|
|
| procedure_detail_source_concept_id |
|
If the PROCEDURE_DETAIL_SOURCE_VALUE is coded in the source data using an OMOP supported vocabulary put the concept id representing the source value here. |
Integer |
No |
No |
Yes |
CONCEPT |
|
| preceding_procedure_detail_id |
Use this field to find the detail that occurred for the procedure prior to the given detail record. |
The PRECEDING_PROCEDURE_DETAIL_ID can be used to link a Details, so they can be treated as arrays (implementation of array with use of uni-directional list of references). Note this is not symmetrical, and there is no such thing as a “following_visit_id”. |
integer |
No |
No |
Yes |
PROCEDURE_DETAIL |
|
| parent_procedure_detail_id |
Use this field to find the detail that subsumes the given detail record. This is used for nesting relationship. |
If there are multiple nested levels to how details are represented in the source, this field can be used to record this relationship. |
integer |
No |
No |
Yes |
PROCEDURE_DETAIL |
|
| procedure_occurrence_id |
Use this field to link the PROCEDURE_DETAIL record to its PROCEDURE_OCCURRENCE. |
Put the PROCEDURE_OCCURRENCE_ID that subsumes the PROCEDURE_DETAIL record here. |
integer |
Yes |
No |
Yes |
PROCEDURE_OCCURRENCE |
|
| operator_concept_id |
The meaning of Concept 4172703 for ‘=’ is identical to omission of a OPERATOR_CONCEPT_ID value. See Measurement table for more info. |
Operators are =, > and these concepts belong to the ‘Meas Value Operator’ domain. Accepted Concepts. |
integer |
No |
No |
Yes |
CONCEPT |
|
| value_as_number |
This is the numerical value of the Detail of the Procedure, if applied. |
|
float |
No |
No |
No |
|
|
| value_as_concept_id |
If the detail values can be categorized, use those values mapped to standard concepts in the ‘Meas Value’ domain. |
The continuous value should go in the VALUE_AS_NUMBER field and the categorical value should be mapped to a standard concept in the ‘Meas Value’ domain and put in the VALUE_AS_CONCEPT_ID field. This is also the destination for the ‘Maps to value’ relationship. |
integer |
No |
No |
Yes |
CONCEPT |
|
| unit_concept_id |
To be used, if the recorded value of procedure detail (parameter) requires a unit, There is currently no recommended unit for individual measurements, i.e. it is not mandatory to represent Hemoglobin a1C measurements as a percentage. UNIT_SOURCE_VALUES should be mapped to a Standard Concept in the Unit domain that best represents the unit as given in the source data. |
There is no standardization requirement for units associated with PROCEDURE_DETAIL_CONCEPT_IDs, however, it is the responsibility of the ETL to choose the most plausible unit. |
integer |
No |
No |
Yes |
CONCEPT |
Unit |
| range_low |
Ranges have the same unit as the VALUE_AS_NUMBER. These ranges are provided by the source and should remain NULL if not given. |
If reference ranges for upper and lower limit of normal as provided (typically by a laboratory) these are stored in the RANGE_HIGH and RANGE_LOW fields. This should be set to NULL if not provided. |
float |
No |
No |
No |
|
|
| range_high |
Ranges have the same unit as the VALUE_AS_NUMBER. These ranges are provided by the source and should remain NULL if not given. |
If reference ranges for upper and lower limit of normal as provided (typically by a laboratory) these are stored in the RANGE_HIGH and RANGE_LOW fields. This should be set to NULL if not provided. |
float |
No |
No |
No |
|
|
| unit_source_value |
This field houses the verbatim value from the source data representing the unit of the PROCEDURE_DETAIL value that occurred. |
This code is mapped to a Standard Condition Concept in the Standardized Vocabularies and the original code is stored here for reference. |
varchar(50) |
No |
No |
No |
|
|
| unit_source_concept_id |
This is the concept representing the UNIT_SOURCE_VALUE and may not necessarily be standard. This field is discouraged from use in analysis. For more info see Measurement table. |
If the UNIT_SOURCE_VALUE is coded in the source data using an OMOP supported vocabulary put the concept id representing the source value here. |
integer |
No |
No |
Yes |
CONCEPT |
|
| value_source_value |
This field houses the verbatim result value of the Procedure detail value from the source data . |
If both a continuous and categorical result are given in the source data such that both VALUE_AS_NUMBER and VALUE_AS_CONCEPT_ID are both included, store the verbatim value that was mapped to VALUE_AS_CONCEPT_ID here. |
varchar(50) |
No |
No |
No |
|
|
The full script here:
– Table: public.procedure_detail
DROP TABLE IF EXISTS public.procedure_detail;
-- Table structure. ETL: Populate field procedure_detail_concept_id with concept in domain Procedure.
CREATE TABLE public.PROCEDURE_DETAIL (
procedure_detail_id integer NOT NULL,
person_id integer NOT NULL,
procedure_detail_concept_id integer NOT NULL,
procedure_detail_type_concept_id integer NOT NULL,
provider_id integer NULL,
procedure_detail_source_value varchar(50) NULL,
procedure_detail_source_concept_id Integer NULL,
preceding_procedure_detail_id integer NULL,
parent_procedure_detail_id integer NULL,
procedure_occurrence_id integer NOT NULL,
operator_concept_id integer NULL,
value_as_number NUMERIC NULL,
value_as_concept_id integer NULL,
unit_concept_id integer NULL,
range_low NUMERIC NULL,
range_high NUMERIC NULL,
unit_source_value varchar(50) NULL,
unit_source_concept_id integer NULL,
value_source_value varchar(50) NULL
);
-- PRIMARY KEY
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT xpk_PROCEDURE_DETAIL PRIMARY KEY (procedure_detail_id);
-- INDICES (by person, concept and occurrence)
CREATE INDEX idx_procedure_det_person_id_1 ON public.procedure_detail (person_id ASC);
CLUSTER public.procedure_detail USING idx_procedure_det_person_id_1 ;
CREATE INDEX idx_procedure_det_concept_id_1 ON public.procedure_detail (procedure_detail_concept_id ASC);
CREATE INDEX idx_procedure_det_occ_id ON public.procedure_detail (procedure_occurrence_id ASC);
-- CONSTRAINTS
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_person_id FOREIGN KEY (person_id) REFERENCES public.PERSON (PERSON_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_procedure_detail_concept_id FOREIGN KEY (procedure_detail_concept_id) REFERENCES public.CONCEPT (CONCEPT_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_procedure_detail_type_concept_id FOREIGN KEY (procedure_detail_type_concept_id) REFERENCES public.CONCEPT (CONCEPT_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_provider_id FOREIGN KEY (provider_id) REFERENCES public.PROVIDER (PROVIDER_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_procedure_detail_source_concept_id FOREIGN KEY (procedure_detail_source_concept_id) REFERENCES public.CONCEPT (CONCEPT_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_preceding_procedure_detail_id FOREIGN KEY (preceding_procedure_detail_id) REFERENCES public.PROCEDURE_DETAIL (PROCEDURE_DETAIL_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_parent_procedure_detail_id FOREIGN KEY (parent_procedure_detail_id) REFERENCES public.PROCEDURE_DETAIL (PROCEDURE_DETAIL_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_procedure_occurrence_id FOREIGN KEY (procedure_occurrence_id) REFERENCES public.PROCEDURE_OCCURRENCE (PROCEDURE_OCCURRENCE_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_operator_concept_id FOREIGN KEY (operator_concept_id) REFERENCES public.CONCEPT (CONCEPT_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_value_as_concept_id FOREIGN KEY (value_as_concept_id) REFERENCES public.CONCEPT (CONCEPT_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_unit_concept_id FOREIGN KEY (unit_concept_id) REFERENCES public.CONCEPT (CONCEPT_ID);
ALTER TABLE public.PROCEDURE_DETAIL ADD CONSTRAINT fpk_PROCEDURE_DETAIL_unit_source_concept_id FOREIGN KEY (unit_source_concept_id) REFERENCES public.CONCEPT (CONCEPT_ID);
