Documentation
Common Data Model (CDM)
Convert Database to CDM (ETL)
Tool Specific Documentation
Sidebar
documentation:next_cdm:multi_level_linkage
This is an old revision of the document!
CDM Multi-Level Associations
- Proposer: Clair Blacketer
Proposal
Add a table that allows for two or more levels of association or linkage between CONCEPT_IDs. This would mostly benefit data sources that are not claims or EHR based. The specific use case I am thinking of is related to the NHANES dataset; a U.S. based survey that combines both health and nutrition information. I cannot currently store the nutrition information effectively because all the CDM tables have one level of association. Adding in a table with two or more levels would allow me to use the nutritional information effectively. For example:
Patient_id -> Date of Meal -> Food item consumed -> Calories in Food item
Patient_id -> Date of Meal -> Food item consumed -> Grams of Fat in Food
This file was taken straight from the NHANES website and shows how the dietary data are constructed. Using the current CDM and assuming they would be routed to the OBSERVATION table 'Respondent sequence number' is PERSON_ID and then 'USDA food code' could be mapped to OBSERVATION_CONCEPT_ID (This also assumes that the food codes would be added to the vocabulary. If not, the food code would go in OBSERVATION_SOURCE_VALUE and OBSERVATION_CONCEPT_ID would be mapped to 0). The problem with this is then I would only be able to see what foods a person ate on a day but I would not know anything about those foods (grams, calories, protein, etc.).
My proposal is that we add an optional observation table (or something similar) to the CDM that allows for a second CONCEPT_ID that would work in relation to the first CONCEPT_ID.
| 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_id1 | Yes | integer | A foreign key to the standard observation concept identifier in the Standardized Vocabularies. (e.g., USDA food code) |
| observation_concept_id2 | Yes | integer | A foreign key to the standard observation concept identifier in the Standardized Vocabularies. (e.g., grams of Carbohydrates in the food item) |
| observation_date | Yes | date | The date of the observation. |
| observation_time | No | time | The 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. |
| 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 |
documentation/next_cdm/multi_level_linkage.1452175520.txt.gz · Last modified: 2016/01/07 14:05 by clairblacketer
