| OHDSI Home | Forums | Wiki | Github |
We have multiple provider_ids and multiple care_site_ids per visit_occurrence. How do others include this data in the CDM? Add extra columns in the visit_occurrence table? Or would it be better to link visit_occurrence_id to multiple entries in the care_site and provider tables? However, visit_occurrence_id is not a foreign key in those tables. Other options?
Thanks,
Melanie
Here is an option. Create a new tables that links the visit_occurrence to
the provider table, same for visit and care site.
Table would look like
create table visit_provider
( visit_occurrence_id integer NOT NULL
, provider_id integer NOT NULL
, any other provider adjective such as admitting, servicing …
)
Where the visit_occurrence_id is a foreign key to the visit_occurrence
table and the provider_id is a foreign key to the provider table.
The primary key will be the combination of visit_occurrence_id and
provider_id.
Now you can have the multiple providers per visit. In addition, if there
is some additional attribute about the provider for the visit you have a
place for that information. This will not break any OMOP conventions,
since they will not know about the new table. If there is a primary
provider, such as admitting or servicing, you could duplicate the the data
and put that provider id directly in the visit table as well as the new
visit_provider table.
In line with @DTorok 's suggestion, here’s a table spec we’re testing out in PEDSnet to get at the multiple care sites and levels of care problem for inpatient stays; adding provider info would be pretty straightforward. We’re planning to kick the metaphorical tires on this occurence table and a paired era table for a few months, and if it looks like it meets our needs, we’ll come back to the CDM working group with a formal proposal (and a better name, if I can think of one).
[ Doesn’t appear the forum understands table layout in Markdown - just paste into your favorite markdown previewer for a much more readable table.]
###ADT_OCCURRENCE
The adt_occurrence table contains information about distinct admission, discharge, or transfer events that occur as part of a clinical visit. The typical use case is to identify portions of an inpatient admission that represent different levels of care or locations within a facility, but it can be used for additional characteristics of a visits (e.g. specialty consultation). The time of each event must fall between the start and end times of the associated visit_occurrence. This table is CUSTOM to Pedsnet.
| Field | NOT Null Constraint | Network Requirement | Data Type | Description | PEDSnet Conventions |
|---|---|---|---|---|---|
| adt_occurrence_id | Yes | Yes | Integer | A unique identifier for each ADT event. | This is not a value found in the EHR. Sites may choose to use a sequential value for this field |
| person_id | Yes | Yes | Integer | A foreign key identifier to the person for whom the visit is recorded. | |
| visit_occurrence_id | Yes | Yes | Integer | A foreign key identifier to the visit containing this event. | |
| adt_date | Yes | Yes | Date | The date of the adt event | |
| adt_time | Yes | Yes | Datetime | The datetime of the adt event | No date shifting. Full date and time. If there is no time associated with the date assert midnight for the start time. |
| care_site_id | No | Provide when available | Integer | A foreign key to the care site in which this adt event occurred. | |
| service_concept_id | Yes | Yes | Integer | A foreign key that refers to a adt event service concept identifier in the vocabulary. This concept describes the type of service associated with this adt event. |
select * from concept where vocabulary_id =‘PEDSnet’ and concept_class_id=‘Service Type’ and standard_concept=‘S’ yields 14 valid concept_ids. In PEDSnet CDM v2.4, only the NICU,CICU and PICU services are included.The value set available for PEDSnet includes:
|
| adt_type_concept_id | No | Provide when available | Integer | A foreign key that refers to an adt event type concept identifier in the vocabulary. This concept describes the type of the adt event. |
select * from concept where vocabulary_id =‘PEDSnet’ and concept_class_id=‘ADT Event Type’ and standard_concept=‘S’ yields 5 valid concept_ids. The value set for PEDSnet includes:
|
| prior_adt_occurrence_id | No | Provide when available | Integer | Foreign key into the adt_occurrence table pointing to the ADT record immediately preceding this record in the event stream for the visit. Must be populated for all but the first ADT even within a visit. | |
| next_adt_occurrence_id | No | Provide when available | Integer | Foreign key into the adt_occurrence table pointing to the ADT record immediately following this record in the event stream for the visit. Must be populated for all but the last ADT even within a visit. | |
| service_source_value | No | Provide when available | Varchar | The source data used to derive the service type for this event. It will typically be a department code from the ADT event. | |
| adt_type_source_value | No | Provide when available | Varchar | The source data used to identify the adt event type | |
This is an interesting related contribution to the discussion of ‘what is a visit’ and sub-parts of an inpatient visit (ICU, ward, etc…). CDM work group discussed this topic for several meetings.
table (via Atom editor)
Thank you for the thorough replies!
Capturing finer granularity for care_site and provider is important for us to deliver thorough and accurate data/information to our customers. We receive requests from providers wanting to know exact location, care_site, and level of care for person, procedure, condition, etc.
As I think through this more, I wonder if the finer granularity of care_site should determine the visit_occurrence_concept_id from an EHR perspective? Or should I think about these two tables separately?
We have also discussed the visits vs. encounters (micro-visits) in this thread here:
We create encounters (or micro-visits) using the visit_occurrence table to capture the granularity of multiple providers and care_sites. The only downside I see is that the number of visits looks inflated when using OHDSI tools. But we just interpret visits as “encounters”. Just another ETL option you might want to consider.
I was thinking (and I am new to this, so please correct me if I am off) that the care_site would contain all the different places (ICU, endoscopy unit, etc.) a patient received procedure_occurrence, drug_exposure, etc. during a visit_occurrence. Visit_occurrence covers the entire time a patient is continuously receiving care and would distinguish a procedure_occurrence that occurred as an outpatient vs. an inpatient. This way we would be able to distinguish between an endoscopy performed as an outpatient versus inpatient procedure through the visit_occurrence table and if it was performed on a specific ward/unit/area of hospital in the care_site (ICU, endoscopy lab) during a visit_occurrence.
This discussion is helpful for me to wrap my head around the CDM and how to best use it for our specific needs while also contributing to broader population health.
The CARE_SITE_ID in CARE_SITE table “contains a list of uniquely identified institutional (physical or organizational) units where healthcare delivery is practiced (offices, wards, hospitals, clinics, etc.)”. We consider the combination of location_id and place_of_service_concept_id as unique natural key. The lowest level of formalized vocabulary for care_site is the HIPAA place of service .
Visit_occurrence table uses a combination of visit_concept_id and care_site_id. Visit_concept_id currently has 4 sub-types (Inpatient, Outpatient, ER and Long-term acute care), while hipaa place of service has about 50 sub-types-- this allows for upto 4*50 = 200 sub-types. But, many are not useful example:
In claims:
So, I dont think there is a way, using the standard vocabulary, to specify if a care-sites is an ICU, endoscopy-unit etc. The lowest level of granularity is determined by these constraints.
Unfortunately, the Place of Service codes do not have the necessary granularity. The PEDSnet value set that @bailey lists above is closer. And so are the licensed NUBC UB-04 codes.
Thanks!
Agree. Are PEDSnet value set custom developed or sourced from another vocabulary/ontology developer? Are there any vocabulary/ontology in HL7/FHIR that may be used?
@Gowtham_Rao Unfortunately, the PEDSnet codes are custom - an admixture of sort-of-CPT/HCPCS-style level of care and sort-of-CMS-style location of care, looking at the anaytic use cases we have. But I’d be happy to move towards a standard if there’s one out there that we’ve missed!
Thank you.
We have two levels already defined: visit_concept and place_of_service, like described above. What I think we are talking about is a third level – a way to document the actual “service” provided that is nested within the place_of_service.
I.e. in a outpatient visit_concept, a Emergency room place_of service, an acute care ‘service’ was provided. Or in an inpatient visit_concept_id, an inpatient place of service, an CICU “service” was provided.
Nested within the service – (fourth level) – procedures may be performed (procedure occurrence table), or condition_occurrence, or drug_occurrence, or measurement may happen. Like in the ER above, resuscitation procedure, or CICU there was blood transfusion.
A hiearachial nested convention like this will solve 1000s of analytic use cases. One formalized convention for “service” is documented here
https://www.hcup-us.ahrq.gov/toolssoftware/util_flags/utilflag.jsp
By adopting something like this – we get upto four levels.
Visit_concept * place of service * service type * (procedure, condition, drug, measurement, observation) = pretty granular information
Friends:
Very interesting proposal by @Gowtham_Rao. The question is where would these 3 levels live? In the VISIT_OCCURRENCE? Then we would have a ton of such nano-visits, as poined out above. Or in the clinical tables (procedure, condition, drug, measurmeent, observation)?
If we want to do the latter, we could do the RxNorm thing, permute the three levels and give each combination, and those where a level or two are missing, its own concept_id. That would then go into the service_concept_id. The VISIT_OCCURRENCE table could then be turned into something like @bailey’s ADT thing.
Should we try that out in a real use case?
@bailey: Just to nag a little: You really don’t need ‘Unknown’, ‘Other’, ‘No information’. They are called “flavors of null”. We have a Concept for these with the concept_id=0. Also, you probably only need one of prior_adt_occurrence_id and next_adt_occurrence_id. Otherwise, you create potential contradictions in the data.
Yes to real use case
