User Tools

Site Tools


documentation:next_cdm:multi_level_linkage

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
Last revision Both sides next revision
documentation:next_cdm:multi_level_linkage [2016/01/07 13:59]
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, they 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 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^ 
 +|food_concept_id|Yes|integer|A foreign key to the Concept in the CONCEPT table representing the identifier for the USDA food code.| 
 +|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.).| 
 +|amount_value|No|float|The numeric value associated with the amount of the nutrient present in the food| 
 +|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| 
 +|valid_start_date|Yes|date|The date when the food concept was first recorded| 
 +|valid_end_date|Yes|date|The date when the concept became invalid because it was deleted or superseded by a new Concept| 
 +|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.| 
documentation/next_cdm/multi_level_linkage.txt · Last modified: 2017/03/01 17:06 by clairblacketer