Represent granular encounters or microvisits in a new VISIT_DETAIL table

[clairblacketer]

Proposal for Visit_detail: Represent granular encounters or microvisits

• Requester: Gowtham Rao

• Discussions: forum post 1, forum post 2

  Proposal

• Current VISIT_OCCURRENCE table does not meet use-cases that need the granular information, such as movement of person while in a hospital.
• The intent of this proposal is to capture detail information about a record in visit_occurrence. Examples of detail information may be encounters, micro-visits etc., and will be collected as is from the source data.
• We propose a new VISIT_DETAIL table with a structure that is similar to current VISIT_OCCURRENCE table. For every record in visit_occurrence there maybe 0 or more records in visit_detail.
• Records in visit_detail will be related to each other sequentially or hierarchically, AND will be related to visit_occurrence table (using chaining/sequential method or parent-child/part-of).
• All information will belong to the domain visit.
• Example: an entire inpatient stay maybe one record in visit_occurrence table. This may have one or more detail information such as ER, ICU, medical floor, rehabilitation floor etc. Each of these visit_details may have different start/end date-times, different concept_id's and fact_id's - that would be separate record in visit_detail with a FK link to visit_occurrence. Each record within visit_detail maybe related to each other, sequentially –> ER leading to ICU leading to medical floor, leading to rehabilitation, or in hierarchical parent-child visit –> a visit for dialysis while in ICU.

New VISIT_DETAIL table

This table will have the same structure as current VISIT_OCCURRENCE table, except for two changes:

• Two new foreign keys pointing to itself (visit_detail_parent_id) and to visit_occurrence table (visit_occurrence_id)
• Removal of _date fields.

Field	Required	Type	Description
visit_detail_id	Yes	integer	A unique identifier for each Person's visit-detail at a healthcare provider.
person_id	Yes	integer	A foreign key identifier to the Person for whom the visit is recorded. The demographic details of that Person are stored in the PERSON table.
visit_detail_concept_id	Yes	integer	A foreign key that refers to a visit Concept identifier in the Standardized Vocabularies.
visit_start_date	Yes	date	The start date of the visit.
visit_start_datetime	Yes	datetime	The date and time of the visit-detail started.
visit_end_date	Yes	date	The end date of the visit.
visit_end_datetime	No	datetime	The date and time of the visit end.
visit_type_concept_id	Yes	integer	A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of source data from which the visit record is derived.
provider_id	No	integer	A foreign key to the provider in the provider table who was associated with the visit.
care_site_id	No	integer	A foreign key to the care site in the care site table where visit occurred
admitting_source_concept_id	No	integer	A foreign key to the predefined concept in the Place of Service Vocabulary reflecting the admitting source for a visit.
discharge_to_concept_id	No	integer	A foreign key to the predefined concept in the Place of Service Vocabulary reflecting the discharge disposition (destination) for a visit.
preceding_visit_detail_id	No	integer	A foreign key to the visit_occurrence table record of the visit immediately preceding this visit.
visit_source_value	No	string	The source code for the visit as it appears in the source data.
visit_source_concept_id	No	Integer	A foreign key to a Concept that refers to the code used in the source.
admitting_source_value	No	string	The source code for the admitting source as it appears in the source data.
discharge_to_source_value	No	string	The source code for the discharge disposition as it appears in the source data.
visit_detail_parent_id	No	integer	A foreign key to the visit_detail table record to represent the immediate parent visit-detail record.
visit_occurrence_id	Yes	integer	A foreign key that refers to the record in the visit_occurrence table

---

Relationship between VISIT_DETAIL and clinical events tables We will add a new optional foreign key pointing from a clinical-event table to visit_detail table.

PROCEDURE_OCCURRENCE

Field	Required	Type	Description
procedure_occurrence_id	Yes	integer	A system-generated unique identifier for each Procedure Occurrence.
person_id	Yes	integer	A foreign key identifier to the Person who is subjected to the Procedure. The demographic details of that Person are stored in the PERSON table.
procedure_concept_id	Yes	integer	A foreign key that refers to a standard procedure Concept identifier in the Standardized Vocabularies.
procedure_date	Yes	date	The date on which the Procedure was performed.
procedure_datetime	No	datetime	The date and time on which the Procedure was performed.
procedure_type_concept_id	Yes	integer	A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of source data from which the procedure record is derived.
modifier_concept_id	No	integer	A foreign key to a Standard Concept identifier for a modifier to the Procedure (e.g. bilateral)
quantity	No	integer	The quantity of procedures ordered or administered.
provider_id	No	integer	A foreign key to the provider in the provider table who was responsible for carrying out the procedure.
visit_occurrence_id	No	integer	A foreign key to the visit in the visit table during which the Procedure was carried out.
visit_detail_id	No	integer	A foreign key to the visit in the visit-detail table during which the Procedure was carried out.
procedure_source_value	No	varchar(50)	The source code for the Procedure as it appears in the source data. This code is mapped to a standard procedure Concept in the Standardized Vocabularies and the original code is, stored here for reference. Procedure source codes are typically ICD-9-Proc, CPT-4, HCPCS or OPCS-4 codes.
procedure_source_concept_id	No	integer	A foreign key to a Procedure Concept that refers to the code used in the source.
qualifier_source_value	No	varchar(50)	The source code for the qualifier as it appears in the source data.

DRUG_EXPOSURE

Field	Required	Type	Description
drug_exposure_id	Yes	integer	A system-generated unique identifier for each Drug utilization event.
person_id	Yes	integer	A foreign key identifier to the person who is subjected to the Drug. The demographic details of that person are stored in the person table.
drug_concept_id	Yes	integer	A foreign key that refers to a Standard Concept identifier in the Standardized Vocabularies for the Drug concept.
drug_exposure_start_date	Yes	date	The start date for the current instance of Drug utilization. Valid entries include a start date of a prescription, the date a prescription was filled, or the date on which a Drug administration procedure was recorded.
drug_exposure_start_datetime	No	datetime	The start date and time for the current instance of Drug utilization. Valid entries include a start date of a prescription, the date a prescription was filled, or the date on which a Drug administration procedure was recorded.
drug_exposure_end_date	No	date	The end date for the current instance of Drug utilization. It is not available from all sources.
drug_exposure_end_datetime	No	datetime	The end date and time for the current instance of Drug utilization. It is not available from all sources.
drug_type_concept_id	Yes	integer	A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of Drug Exposure recorded. It indicates how the Drug Exposure was represented in the source data.
stop_reason	No	varchar(20)	The reason the Drug was stopped. Reasons include regimen completed, changed, removed, etc.
refills	No	integer	The number of refills after the initial prescription. The initial prescription is not counted, values start with 0.
quantity	No	float	The quantity of drug as recorded in the original prescription or dispensing record.
days_supply	No	integer	The number of days of supply of the medication as recorded in the original prescription or dispensing record.
sig	No	clob	The directions ("signetur") on the Drug prescription as recorded in the original prescription (and printed on the container) or dispensing record.
route_concept_id	No	integer	A foreign key to a predefined concept in the Standardized Vocabularies reflecting the route of administration.
effective_drug_dose	No	float	Numerical value of Drug dose for this Drug Exposure record.
dose_unitconcept id	No	integer	A foreign key to a predefined concept in the Standardized Vocabularies reflecting the unit the effective_drug_dose value is expressed.
lot_number	No	varchar(50)	An identifier assigned to a particular quantity or lot of Drug product from the manufacturer.
provider_id	No	integer	A foreign key to the provider in the provider table who initiated (prescribed or administered) the Drug Exposure.
visit_occurrence_id	No	integer	A foreign key to the visit in the visit table during which the Drug Exposure was initiated.
visit_detail_id	No	integer	A foreign key to the visit in the visit-detail table during which the Drug Exposure was initiated.
drug_source_value	No	varchar(50)	The source code for the Drug as it appears in the source data. This code is mapped to a Standard Drug concept in the Standardized Vocabularies and the original code is, stored here for reference.
drug_source_concept_id	No	integer	A foreign key to a Drug Concept that refers to the code used in the source.
route_source_value	No	varchar(50)	The information about the route of administration as detailed in the source.
dose_unit_source_value	No	varchar(50)	The information about the dose unit as detailed in the source.

DEVICE_EXPOSURE

Field	Required	Type	Description
device_exposure_id	Yes	integer	A system-generated unique identifier for each Device Exposure.
person_id	Yes	integer	A foreign key identifier to the Person who is subjected to the Device. The demographic details of that person are stored in the Person table.
device_concept_id	Yes	integer	A foreign key that refers to a Standard Concept identifier in the Standardized Vocabularies for the Device concept.
device_exposure_start_date	Yes	date	The date the Device or supply was applied or used.
device_exposure_start_datetime	No	datetime	The date and time the Device or supply was applied or used.
device_exposure_end_date	No	date	The date the Device or supply was removed from use.
device_exposure_end_datetime	No	datetime	The date and time the Device or supply was removed from use.
device_type_concept_id	Yes	integer	A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the type of Device Exposure recorded. It indicates how the Device Exposure was represented in the source data.
unique_device_id	No	varchar(50)	A UDI or equivalent identifying the instance of the Device used in the Person.
quantity	No	integer	The number of individual Devices used for the exposure.
provider_id	No	integer	A foreign key to the provider in the PROVIDER table who initiated of administered the Device.
visit_occurrence_id	No	integer	A foreign key to the visit in the VISIT table during which the device was used.
visit_detail_id	No	integer	A foreign key to the visit in the VISIT_DETAIL table during which the device was used.
device_source_value	No	varchar(50)	The source code for the Device as it appears in the source data. This code is mapped to a standard Device Concept in the Standardized Vocabularies and the original code is stored here for reference.
devicesource concept_id	No	integer	A foreign key to a Device Concept that refers to the code used in the source.

CONDITION_OCCURRENCE

Field	Required	Type	Description
condition_occurrence_id	Yes	integer	A unique identifier for each Condition Occurrence event.
person_id	Yes	integer	A foreign key identifier to the Person who is experiencing the condition. The demographic details of that Person are stored in the PERSON table.
condition_concept_id	Yes	integer	A foreign key that refers to a Standard Condition Concept identifier in the Standardized Vocabularies.
condition_start_date	Yes	date	The date when the instance of the Condition is recorded.
condition_start_datetime	No	datetime	The date and time when the instance of the Condition is recorded.
condition_end_date	No	date	The date when the instance of the Condition is considered to have ended.
condition_end_datetime	No	date	The date when the instance of the Condition is considered to have ended.
condition_type_concept_id	Yes	integer	A foreign key to the predefined Concept identifier in the Standardized Vocabularies reflecting the source data from which the condition was recorded, the level of standardization, and the type of occurrence.
stop_reason	No	varchar(20)	The reason that the condition was no longer present, as indicated in the source data.
provider_id	No	integer	A foreign key to the Provider in the PROVIDER table who was responsible for capturing (diagnosing) the Condition.
visit_occurrence_id	No	integer	A foreign key to the visit in the VISIT table during which the Condition was determined (diagnosed).
visit_detail_id	No	integer	A foreign key to the visit in the VISIT_DETAIL table during which the Condition was determined (diagnosed).
condition_status_concept_id	No	integer	A foreign key to the predefined concept in the standard vocabulary reflecting the condition status.
condition_source_concept_id	No	integer	A foreign key to a Condition Concept that refers to the code used in the source.
condition_source_value	No	varchar(50)	The source code for the condition as it appears in the source data. This code is mapped to a standard condition concept in the Standardized Vocabularies and the original code is stored here for reference.
condition_status_source_value	No	varchar(50)

MEASUREMENT

Field	Required	Type	Description
measurement_id	Yes	integer	A unique identifier for each Measurement.
person_id	Yes	integer	A foreign key identifier to the Person about whom the measurement was recorded. The demographic details of that Person are stored in the PERSON table.
measurement_concept_id	Yes	integer	A foreign key to the standard measurement concept identifier in the Standardized Vocabularies.
measurement_date	Yes	date	The date of the Measurement.
measurement_datetime	No	datetime	The date and time of the Measurement. (Some database systems don't have a datatype of time. To accomodate all temporal analyses, datatype datetime can be used (combining measurement_date and measurement_time)[[http://forums.ohdsi.org/t/date-time-and-datetime-problem-and-the-world-of-hours-and-1day/314	Relevant Forum Discussion]]
measurement_type_concept_id	Yes	integer	A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the provenance from where the Measurement record was recorded.
operator_concept_id	No	integer	A foreign key identifier to the predefined Concept in the Standardized Vocabularies reflecting the mathematical operator that is applied to the value_as_number. Operators are <, ≤, =, ≥, >.
value_as_number	No	float	A Measurement result where the result is expressed as a numeric value.
value_as_concept_id	No	integer	A foreign key to a Measurement result represented as a Concept from the Standardized Vocabularies (e.g., positive/negative, present/absent, low/high, etc.).
unit_concept_id	No	integer	A foreign key to a Standard Concept ID of Measurement Units in the Standardized Vocabularies.
range_low	No	float	The lower limit of the normal range of the Measurement result. The lower range is assumed to be of the same unit of measure as the Measurement value.
range_high	No	float	The upper limit of the normal range of the Measurement. The upper range is assumed to be of the same unit of measure as the Measurement value.
provider_id	No	integer	A foreign key to the provider in the PROVIDER table who was responsible for initiating or obtaining the measurement.
visit_occurrence_id	No	integer	A foreign key to the Visit in the VISIT_OCCURRENCE table during which the Measurement was recorded.
visit_detail_id	No	integer	A foreign key to the Visit in the VISIT_DETAIL table during which the Measurement was recorded.
measurement_source_value	No	varchar(50)	The Measurement name as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is stored here for reference.
measurement_source_concept_id	No	integer	A foreign key to a Concept in the Standard Vocabularies that refers to the code used in the source.
unit_source_value	No	varchar(50)	The source code for the unit as it appears in the source data. This code is mapped to a standard unit concept in the Standardized Vocabularies and the original code is stored here for reference.
value_source_value	No	varchar(50)	The source value associated with the content of the value_as_number or value_as_concept_id as stored in the source data.

NOTE

Field	Required	Type	Description
note_id	Yes	integer	A unique identifier for each note.
person_id	Yes	integer	A foreign key identifier to the Person about whom the Note was recorded. The demographic details of that Person are stored in the PERSON table.
note_date	Yes	date	The date the note was recorded.
note_datetime	No	datetime	The date and time the note was recorded.
note_type_concept_id	Yes	integer	A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the type, origin or provenance of the Note.
note_class_concept_id	Yes	integer	A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the HL7 LOINC Document Type Vocabulary classification of the note.
note_title	No	string(250)	The title of the Note as it appears in the source.
note_text	Yes	RBDMS dependent text	The content of the Note.
encoding_concept_id	Yes	integer	A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the note character encoding type.
language_concept_id	Yes	integer	A foreign key to the predefined Concept in the Standardized Vocabularies reflecting the language of the note.
provider_id	No	integer	A foreign key to the Provider in the PROVIDER table who took the Note.
visit_occurrence_id	No	integer	Foreign key to the Visit in the VISIT_OCCURRENCE table when the Note was taken.
visit_detail_id	No	integer	Foreign key to the Visit in the VISIT_DETAIL table when the Note was taken.

OBSERVATION

Field	Required	Type	Description
observation_id	Yes	integer	A unique identifier for each observation.
person_id	Yes	integer	A foreign key identifier to the Person about whom the observation was recorded. The demographic details of that Person are stored in the PERSON table.
observation_concept_id	Yes	integer	A foreign key to the standard observation concept identifier in the Standardized Vocabularies.
observation_date	Yes	date	The date of the observation.
observation_datetime	No	datetime	The date and time of the observation.
observation_type_concept_id	Yes	integer	A foreign key to the predefined concept identifier in the Standardized Vocabularies reflecting the type of the observation.
value_as_number	No	float	The observation result stored as a number. This is applicable to observations where the result is expressed as a numeric value.
value_as_string	No	varchar(60)	The observation result stored as a string. This is applicable to observations where the result is expressed as verbatim text.
value_as_concept_id	No	Integer	A foreign key to an observation result stored as a Concept ID. This is applicable to observations where the result can be expressed as a Standard Concept from the Standardized Vocabularies (e.g., positive/negative, present/absent, low/high, etc.).
qualifier_concept_id	No	integer	A foreign key to a Standard Concept ID for a qualifier (e.g., severity of drug-drug interaction alert)
unit_concept_id	No	integer	A foreign key to a Standard Concept ID of measurement units in the Standardized Vocabularies.
provider_id	No	integer	A foreign key to the provider in the PROVIDER table who was responsible for making the observation.
visit_occurrence_id	No	integer	A foreign key to the visit in the VISIT_OCCURRENCE table during which the observation was recorded.
visit_detail_id	No	integer	A foreign key to the visit in the VISIT_DETAIL table during which the observation was recorded.
observation_source_value	No	varchar(50)	The observation code as it appears in the source data. This code is mapped to a Standard Concept in the Standardized Vocabularies and the original code is, stored here for reference.
observation_source_concept_id	No	integer	A foreign key to a Concept that refers to the code used in the source.
unit_source_value	No	varchar(50)	The source code for the unit as it appears in the source data. This code is mapped to a standard unit concept in the Standardized Vocabularies and the original code is, stored here for reference.
qualifier_source_value	No	varchar(50)	The source value associated with a qualifier to characterize the observation

---

Conventions

Representation of US claim data

US claims data generally has two-levels

• Header/summary data that summarizes the entire claim
• Line/detail that details a claim.

Detail is thus a child of the summary, and for every record in summary there is one or more records in detail. i.e. there will be atleast one FK link from visit_detail to visit_occurrence.

Use Cases

• new table for encounters
• how to represent encounter payer information, capturing more than one care site id per encounter
• purpose of the omop cdm analytics ready or close to source

[parisni]

hi @clairblacketer

11 july is not too late, let me be devil's advocate with my 2 cents on that topic. There is a strong need to get this granularity level into OMOP. to my knowledge, other projects deal with the question of visit granularity:

• i2b2 (1 table=visit_dimension)

  the Visit_Dimension table represents sessions where observations were made. Each row represents one session (also called a visit, event or encounter.) (https://www.i2b2.org/software/projects/datarepo/CRC_Design_Doc_13.pdf)

• FHIR (1 nested table=encounter resource)

  Encounter allows a flexible nesting of Encounters using the partOf element. (https://www.hl7.org/fhir/encounter.html#examples)

• MIMIC (2 tables = admissions + stay)

  stay_id is a generated identifier that is not based on any raw data identifier the hospital and ICU databases are not intrinsically linked and so do not have any concept of an ICU encounter identifier.(https://mimic.physionet.org/mimictables/icustays/)

• this OMOP proposal (2 tables + nested = visit_occurence + visit_detail ) This proposal is clearly the most complicated approach. I understand OMOP goal is analytics, but

  • Do we need this complexity ?
  • Do we need a second table that get exact same fields as the existing one plus a nested field ?
  • Do this really addresses questions of trajectory of patients within services (transitions) ?
  • Do this addresses the question of interoperability and diversity of granularity around the world ?
  • Do this addresses future needs of connected objects at bedside and at home ?

A single nested table proposal should be taken into consideration. Without consideration about what analytical use cases you have, and what SQL queries would solve them it is a bit early to go in one or other direction. (sorry if I missed something about that in both forum post 1 & 2).

An example of single nested table proposal could be visit_occurence + :

• visit_occurence_parent_id (equivalent of FHIR part-of): when NULL then this is a root = macro-visit. The room, bed where the patient is, could also be referenced in that table. references primary key.
• visit_occurence_seq: integer sequence, order of visit (macro or micro or whatever). Would help to analyse the sequence of visits, trajectory...
• visit_occurence_type: the type of visit, macro, micro, room, bed....
• removing those date and keeping datime is a really good idea. I know they are legacy but....

Then it would be two options for fact tables (measurments, notes, condition....):

• adding new fields pointing to different lvl of visit (visit_id, stay_id, room_id, bed_id and so on)
• keeping only one reference to visit_occurence_id, and use recursive queries (https://www.postgresql.org/docs/current/static/queries-with.html). They are complicated, but would always work same time even if you merge data from different granularity model. This would lead to more interoperability at national and international.

[cgreich]

@parisni

All good points. And they have been made during the debate we had. The current proposal is a two-level hierarchy, where both levels are stratified into separate tables, VISIT_OCCURRENCE and VISIT_DETAIL. This is a compromise between a number of conflicting needs:

1. Backwards compatibility. The VISIT_OCCURRENCE table already exists, and it explicitly contains the macro level. If you now want to add new nested more granular records to that table that will overlap in time all existing queries will break. 2. Consistency. We already have two dimensions were things exist in more detail and in more aggregate: DRUG_EXPOSURE/DRUG_ERA and CONDITION_OCCURRENCE/CONDITION_ERA. We didn't call VISIT_OCCURRENCE VISIT_ERA, again for compatibility reasons, but otherwise it is the same idea. 3. Matching of use cases. Instead of creating the mother of all perfect solutions, we want to create a solution that will work in most use cases simply. And that use case is VISIT_OCCURRENCE. The vast majority (I think >95%) of analytics will ask for some medical event in combination with hospitalization, or outside hospitalization, without any regard what exactly happened inside the hospital. VISIT_OCCURRENCE does that perfectly well. 4. Simplicity. Making folks do recursive queries to the same table is not popular. And it is not fast, either. 5. Availability of data. Even the most detailed EHR systems do not give you all detail down to the bed. And even if it did - do we really need the number of the bed that patient was in? Or room? What's the use case for that?

It's a pity you couldn't participate in this debate earlier, because it has been going on for half a year at least, and many of your ideas were brought up. See here, here, here, here and here . Now, we are ready to push it out. If you really feel you want to change the consensus, you will need to provide use cases which will work in your proposal, and will not work in the current proposal.

BTW: We will continue this subject on the next CDM Workgroup meeting next Tuesday the 11th of July. Please come.

Makes sense?

[parisni]

Hey @cgreich

Thanks for this answer. This is very true about the over-complexity of a single nested table, and I understand the backward compatibility concerns. While I am not having the big picture on OMOP, and I haven't seen any post aggregating research uses cases, versus model proposal versus sql queries, your proposal looks to answer my needs, and I am confident it is robust.

About the working on the trajectory, some testing showed me that your design works fine: please consider this link

BTW, I would be glad to meet you on the CDM workgroup, but I am not very clear with the way to do so. Being a European fellow, is there any chance I join you by a web solution & not by phone as described on the link ?

[cgreich]

@parisni:

Yes, typical trajectories are a good use case. Other ones are hospital-acquired infections, efficiency of treatment, occurrence of adverse events, cost of treatment. All these outcomes can be stratified by the VISIT_DETAIL. Benchmarks could be established, and even comparisons between provider institutions could be created.

But again, the vast majority of cases using any of the VISIT_* tables is to find out if a condition was treated in a hospital or outside. Most often, this is a surrogate for severity. An asthma attack the patient treated with a puff from his inhaler is a different kind of thing than a status asthmaticus requiring an ambulance to the A&E and a subsequent hospital stay. Those need to be kept simple (where exists (select 1 from VISIT_OCCURRENCE where person_id=patient_id and visit_start_date=event_date and visit_concept_id in (inpatient, ER, both)))

Where are you located? I can add a local toll-free number for pretty much every country except North Korea. Also, I need your email address.

[clairblacketer]

In the initial proposal _date was removed, in the proposal as it currently is we will keep both _date and _datetime and drop _date in version 6.0.0

[don-torok]

visit_detail_concept_id: Is it possible to add guidance stating the expected concept domain? In fact, it will be an aid to ETL developers if the expected concept domain where provided for all the concept id in Visit Detail.

preceding_visit_detail_id: Description says 'visit immediately preceding this visit'. Should this say the visit_detail immediately preceding this visit_detail? Also, suggest adding guidance for what to put for first visit_detail record in the series (no predecessor), should it be NULL or Zero?

visit_detail_parent_id: Again suggest guidance for top level visit_detail. should the value be 0, NULL, or even the visit_occurrence_id of the associated visit?