documentation:next_cdm:multi_level_linkage
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision | Last revision Both sides next revision | ||
|
documentation:next_cdm:multi_level_linkage [2016/01/07 14:05] clairblacketer |
documentation:next_cdm:multi_level_linkage [2017/03/01 17:03] clairblacketer |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | **CDM Multi-Level Associations** | + | **Food Component Table** |
| * Proposer: Clair Blacketer | * Proposer: Clair Blacketer | ||
| Line 5: | Line 5: | ||
| **Proposal** | **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 [[http://www.cdc.gov/nchs/nhanes.htm|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: | + | Add a table similar to the DRUG_STRENGTH table that would contain information about foods such as calories, fat, and sugar content. The specific use case I am thinking of is related to the [[http://www.cdc.gov/nchs/nhanes.htm|NHANES]] dataset; a U.S. based survey that combines both health and nutrition information. I cannot currently store the nutrition information effectively because it would require two levels of association in any of the event tables. For example: |
| Patient_id -> Date of Meal -> Food item consumed -> Calories in Food item | 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 | Patient_id -> Date of Meal -> Food item consumed -> Grams of Fat in Food | ||
| - | This {{:documentation:next_cdm:nhanes_dr1iff_g_example.xlsx|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.). | + | This {{:documentation:next_cdm:nhanes_dr1iff_g_example.xlsx|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' is 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. | + | My proposal is that we create a reference table similar to the DRUG_STRENGTH table to house this information. This would also require the addition of USDA food codes to the vocabulary, as well the addition of food attributes to the vocabulary. |
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| - | |observation_id|Yes|integer|A unique identifier for each observation.| | + | |food_concept_id|Yes|integer|A foreign key to the Concept in the CONCEPT table representing the identifier for the USDA food code.| |
| - | |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.| | + | |food_component_concept_id|Yes|integer|A foreign key to the Concept in the CONCEPT table representing the identifier for the food component (fat, sugar, etc.).| |
| - | |observation_concept_id1|Yes|integer|A foreign key to the standard observation concept identifier in the Standardized Vocabularies. (e.g., USDA food code)| | + | |amount_value|No|float|The numeric value associated with the amount of the nutrient present in the food| |
| - | |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)| | + | |amount_unit_concept_id|No|integer|A foreign key to the Concept in the CONCEPT table representing the identifier for the Unit for the absolute amount of the nutrient| |
| - | |observation_date|Yes|date|The date of the observation.| | + | |valid_start_date|Yes|date|The date when the food concept was first recorded| |
| - | |observation_time|No|time|The time of the observation.| | + | |valid_end_date|Yes|date|The date when the concept became invalid because it was deleted or superseded by a new Concept| |
| - | |observation_type_concept_id|Yes|integer|A foreign key to the predefined concept identifier in the Standardized Vocabularies reflecting the type of the observation.| | + | |invalid_reason|No|integer|Reason the concept was invalidated. Possible values are 'D' (deleted), 'U' (replaced with an update) or NULL when valid_end_date has the default value.| |
| - | |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.txt · Last modified: 2017/03/01 17:06 by clairblacketer
Page Tools
