OHDSI Home | Forums | Wiki | Github

Visit Detail: wiki & DDL do not match


(Anna Karenina) #1

Hello,

Writing this post in a desperate attempt to grasp what structure Visit Detail table should really have. The wiki description and DDL definition have a number of discrepancies, starting from v5.3.0 when it was first introduced, and the order and names of fields keep changing with every CDM version. Below, wiki content is compared to SQL Server DDL scripts.

CDM v5.3.0

# wiki DDL script
1 visit_detail_id visit_detail_id
2 person_id person_id
3 visit_concept_id visit_detail_concept_id
4 visit_start_date visit_start_date
5 visit_start_datetime visit_start_datetime
6 visit_end_date visit_end_date
7 visit_end_datetime visit_end_datetime
8 visit_type_concept_id visit_type_concept_id
9 provider_id provider_id
10 care_site_id care_site_id
11 visit_source_value admitting_source_concept_id
12 visit_source_concept_id discharge_to_concept_id
13 admitting_source_value preceding_visit_detail_id
14 admitting_source_concept_id visit_source_value
15 discharge_to_source_value visit_source_concept_id
16 discharge_to_concept_id admitting_source_value
17 preceding_visit_detail_id discharge_to_source_value
18 visit_detail_parent_id visit_detail_parent_id
19 visit_occurrence_id visit_occurrence_id
  • DDL has visit_detail_concept_id, although Visit Detail fields were renamed only in v5.3.1.
  • Field order in wiki seems quite logical, but in DDL it’s shuffled in a strange way.

CDM v5.3.1

# wiki DDL script
1 visit_detail_id visit_detail_id
2 person_id person_id
3 visit_detail_concept_id visit_detail_concept_id
4 visit_detail_start_date visit_detail_start_date
5 visit_detail_start_datetime visit_detail_start_datetime
6 visit_detail_end_date visit_detail_end_date
7 visit_detail_end_datetime visit_detail_end_datetime
8 visit_detail_type_concept_id visit_detail_type_concept_id
9 provider_id provider_id
10 care_site_id care_site_id
11 visit_detail_source_value admitting_source_concept_id
12 visit_detail_source_concept_id discharge_to_concept_id
13 admitting_source_value preceding_visit_detail_id
14 admitting_source_concept_id visit_detail_source_value
15 discharge_to_source_value visit_detail_source_concept_id
16 discharge_to_concept_id admitting_source_value
17 preceding_visit_detail_id discharge_to_source_value
18 visit_detail_parent_id visit_detail_parent_id
19 visit_occurrence_id visit_occurrence_id
  • The fields were renamed to match CDM naming convention, cool.
  • But the shuffled second part retains the same order as in 5.3.0?

CDM v6.0.0

# wiki DDL script
1 visit_detail_id visit_detail_id
2 person_id person_id
3 visit_detail_concept_id visit_detail_concept_id
4 visit_detail_start_date visit_detail_start_date
5 visit_detail_start_datetime visit_detail_start_datetime
6 visit_detail_end_date visit_detail_end_date
7 visit_detail_end_datetime visit_detail_end_datetime
8 visit_detail_type_concept_id visit_detail_type_concept_id
9 provider_id provider_id
10 care_site_id care_site_id
11 visit_detail_source_value discharge_to_concept_id
12 visit_detail_source_concept_id admitted_from_concept_id
13 admitted_from_source_value admitted_from_source_value
14 admitted_from_concept_id visit_detail_source_value
15 discharge_to_source_value visit_detail_source_concept_id
16 discharge_to_concept_id discharge_to_source_value
17 preceding_visit_detail_id preceding_visit_detail_id
18 visit_detail_parent_id visit_detail_parent_id
19 visit_occurrence_id visit_occurrence_id
  • The names match, but the order does not, even though it’s different from 5.3.x now.
  • Also, wiki has integer data type for [entity]_id fields and DDL uses bigint – which is the right one?

The Ultimate Question

What should be the source of truth in this situation – wiki or DDL?

Thank you.


t