Canadian Nutrient File - Database Structure
Compilation of Canadian Food Composition Data
2015
Nutrition Research Division
Food Directorate
Health Products and Food Branch
Health Canada
Table of contents
Introduction
The downloadable files comprising the Canadian Nutrient File database are relational files available in two formats: Excel files and csv files. In order to view and utilize these files one must both own and be familiar with a database management software program or a spreadsheet program of sufficient capacity. New for this version we will also be offering the database as a downloadable Microsoft Access file in which the tables have been joined and common queries created.
To save space and speed up downloads; the files are zipped into one large file with a file name ending with '.exe'. This is the file that is downloaded to whatever directory the user specifies. After downloading, go to that directory and double click on the exe file; this will extract the many files comprising the CNF database.
Some database management programs require you to "open" these files into their programs while others require them to be "Imported". Once they are imported, the corresponding tables can be joined using the common fields indicated in the File Relationships section below.
New for this edition
New food code
Over time, CNF users tend to get used to the numeric code associated to certain foods, and start to refer to these foods using their code. In the CNF database, this familiar, unique food code (FD_CODE in the FOOD_NAME table) is not the primary key.
All primary key fields end with _ID (FC_ID) in the CNF database in order to be easily identified.
Field | Description of field | New role in the database | Will appear in the web search application |
FoodID | Food ID field or food identifier | This is the primary key and the link for joining related tables | No |
Food code | Food code | This is the user food code | Yes |
It is recommended that the Food ID and other primary keys be used only to link tables and that the Food Code be displayed and used for searches.
Remember that all of the nutrient data is stored per 100 g of the food. If you want to manipulate the data to display weights per portion, this can be done through some sophisticated calculated field programming.
If you are not familiar with these terms you may want to buy a nutrient analysis software program which features 2015 CNF data.
2015 File Relationships
File Relationships
The 2015 file relationship diagram shows the links between the twelve files comprising the Canadian Nutrient File and identifies the primary keys used to link the files together. There are five principal files which are shaded in grey: Food Name, Nutrient Amount, Yield Amount, Refuse Amount and Conversion Factor. The seven support files are: Yield Name, Refuse Name, Measure Name, Nutrient Name, Food Group, Food Source and Nutrient Source.
The Food Name file links to the Yield Amount, the Refuse Amount, the Conversion Factor and Nutrient Amount files using the FoodID field. The Food Name file links to the Food Group file using the FoodGroupID field. The Food Name file links to the Food Source file using the FoodSourceID field. The Nutrient Amount file links to the Nutrient Source file using the NutrientSourceID field. The Nutrient Amount file links to the Nutrient Name file using the NutrientNameID field. The Yield Amount file links to Yield Name file using the YieldID field. The Refuse Amount links to the Refuse Name file using the RefuseID field. The Conversion Factor file links to Measure Name using the MeasureID field.

Name | File name | File description |
---|---|---|
Food name | Food name | This is a principal file. It stores information about each food in the database. It contains a description of each food in English and French as well as dates and comments. |
Nutrient amount | Nutrient amount | This is the main file. It uses information (by linking) from the food name table (among others) to identify which nutrients and amounts are recorded for that food. |
Conversion factor | Conversion factor | This is a principal file. This file contains portion size conversion factors. The conversion factors are food specific multipliers by which the nutrient values for each food may be multiplied to give the nutrients in described portions. |
Refuse amount | Refuse amount | This is a principal file. This file contains the percent of refuse, or inedible portion, for each food. |
Yield amount | Yield amount | This is a principal file. This file contains the yield from refuse and/or cooking losses assigned to certain foods. These yields are most often used for food inventory purposes. |
Food group | Food group | This file is a support or "list" table that is used to link to the food name table. It contains a list of 23 different group headings (in English and French) based on similar characteristics of the foods. |
Food source | Food source | This file is a support or "list" table that is used to link to the food name table. It contains a list of several food sources (in English and French) that foods can be grouped on. |
Nutrient name | Nutrient name | This file is a support or "list" table that contains the list of nutrients (in English and French) used in the NT_AMT file, with which it is linked. |
Nutrient source | Nutrient Source | This file is a support or "list" table that is used to link to the nutrient amount file. It contains a list of several sources and/or types of nutrient data (in English and French). |
Measure | Measure name | This file is a support or "list" table that is used to link to the conv fac table. It contains a list of measures (in English and French). |
Refuse name | Refuse name | This file is a support or "list" table that is used to link to the refuse table. It contains a list of refuse types. |
Yield name | Yield name | This file is a support or "list" table that is used to link to the yield table. It contains a list of yield types or yield descriptions (in English and French). |
Principal files
I. Food name file:
N = numeric - # of characters / # of decimals (if decimal)
T = text
D = date
n/a = not applicable
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
FoodID | N | 11 | Sequential number generated by the database identifying the food records. This is the primary key. | Nutrient amount conversion factor yield amount |
FoodCode | N | 8 | Identifier code for the foods. In the 2007b version this was the FD_C | n/a |
FoodGroupID | N | 15 | Sequential number generated by the database for the food groups | Food group |
FoodSourceID | N | 15 | Sequential number generated by the database for the food sources. | Food source |
FoodDescription | T | 255 | Complete food name in English | n/a |
FoodDescriptionF | T | 255 | Complete food name in French | n/a |
CountryCode | N | 20 | Corresponds to the USDA NDB code | n/a |
FoodDateOfEntry | D | yyyy/mm/dd | Date the food name data was entered into the database | n/a |
FoodDateOfPublication | D | yyyy/mm/dd | Date the food name data was originally published | n/a |
ScientificName | T | 100 | Scientific name of the food | n/a |
II. Nutrient amount file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
FoodID | N | 8 | Sequential number generated by the database identifying the food records. | Food name |
NutrientNameID | N | 4 | Sequential number generated by the database for the nutrient names | Nutrient name |
NutrientValue | N | 12/5 | Mean value in 100g, edible portion. (The number of decimal places does not reflect the accuracy of the data.) | n/a |
StandardError | N | 8/4 | Standard error of the mean | n/a |
NumberOfObservations | N | 6 | Number of samples | n/a |
NutrientSourceID | N | 15 | Identifier code generated by the database for the nutrient source | Nutrient source |
NutrientDateEntry | D | yyyy/mm/dd | Date the data was entered into the database | n/a |
III. Conversion factor file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
FoodID | N | 8 | Sequential number generated by the database identifying the food records. | Food name |
MeasureID | N | 10 | Sequential number generated by the database for the measure descriptions | Measure name |
ConversionFactorValue | N | 10 | The factor by which one would multiply the nutrient per 100g to obtain nutrient amounts per the measure described (the weight of that food in the measure described divided by 100) | n/a |
ConvFactorDateOfEntry | D | yyyy/mm/dd | Date the data was entered into the database | n/a |
IV. Refuse amount file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
FoodID | N | 8 | Sequential number generated by the database identifying the food records. | Food name |
RefuseID | N | 10 | Sequential number generated by the database for the refuse descriptions | Refuse name |
RefuseAmount | N | 9/5 | Percent refuse | n/a |
RefuseDateOfEntry | D | yyyy/mm/dd | Date the data was entered into the database | n/a |
V. Yield amount file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
FoodID | N | 8 | Sequential number generated by the database identifying the food records. | Food name |
YieldID | N | 10 | Sequential number generated by the database for the yield descriptions | Yield name |
YieldAmount | N | 9/5 | Yield from refuse and/or cooking losses | n/a |
YieldDateOfEntry | D | yyyy/mm/dd | Date the data was entered into the database | n/a |
Support files:
A. Food group file:
N = numeric - # of characters / # of decimals (if decimal)
T = text
D = date
n/a = not applicable
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
FoodGroupID | N | 15 | Sequential number generated by the database for the food groups | Food name |
FoodGroupCode | N | 15 | Identifier code for the Canadian Nutrient File food groups. There are 23 different CNF food groups | n/a |
FoodGroupName | T | 200 | Food group name in English | n/a |
FoodGroupNameF | T | 200 | Food group name in French | n/a |
B. Food source file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
FoodSourceID | N | 15 | Sequential number generated by the database for the food sources | Food name |
FoodSourceCode | N | 15 | Identifier code for the food sources. Food sources give an indication as to Canadian content | n/a |
FoodSourceDescription | T | 200 | Food Source description in English | n/a |
FoodSourceDescriptionF | T | 200 | Food Source description in French | n/a |
C. Nutrient name file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
NutrientNameID | N | 4 | Sequential number generated by the database for the nutrient names | Nutrient amount |
NutrientCode | N | 15 | Identifier code for nutrient name descriptions | n/a |
NutrientSymbol | T | 10 | Nutrient symbol or abbreviation of the nutrients. They may differ from international nomenclature. | n/a |
Unit | T | 8 | Unit of measure (e.g., mg, g, mcg) | n/a |
NutrientName | T | 200 | Nutrient name in English | n/a |
NutrientNameF | T | 200 | Nutrient name in French | n/a |
Tagname | T | 20 | International Network of Food Data Systems (INFOODS) Tagnames. A unique Abbreviation for a food component developed by INFOODS to aid in the interchange of data. | n/a |
NutrientDecimals | N | 15 | Number of decimal places used in the rounding of the nutrient value | n/a |
D. Nutrient source file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
NutrientSourceID | N | 15 | Sequential number generated by the database for the nutrient sources | Nutrient amount |
NutrientSourceCode | N | 15 | Identifier code for nutrient source descriptions. | n/a |
NutrientSourceDescription | T | 200 | Nutrient source description in English | n/a |
NutrientSourceDescriptionF | T | 200 | Nutrient source description in French | n/a |
E. Measure name file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
MeasureID | N | 10 | Sequential number generated by the database for the measure descriptions | Conversion factor |
MeasureName | T | 200 | Measure description in English | n/a |
MeasureNameF | T | 200 | Measure description in French | n/a |
F. Refuse name file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
RefuseID | N | 10 | Sequential number generated by the database for the refuse descriptions | Refuse amount |
RefuseName | T | 200 | Refuse description in English | n/a |
RefuseNameF | T | 200 | Refuse description in French | n/a |
G. Yield name file:
Field name | Field type | Size | Field description | Links |
---|---|---|---|---|
YieldID | N | 10 | Sequential number generated by the database for the yield descriptions | Yield amount |
YieldName | T | 200 | Yield description in English | n/a |
YieldNameF | T | 200 | Yield description in French | n/a |
Update files list
For the 2015 version we are offering update files that track records that have been changed, added or deleted since the release of the 2010 version of the CNF. These files have the same field name structure as the files of the main database.
File Name | File description | Comment |
---|---|---|
Food name add | Added food names | no comments.no comments. |
Nutrient name add | Added nutrient names | no comments.no comments. |
Nutrient amount add | Added nutrient amounts | Includes nutrient values added to already existing foods as well as new foods |
Measure name add | Added measures | no comments. |
Conversion factor add | Added conversion factors | Includes conversion factors values added to already existing foods as well as new foods |
Refuse name add | Added refuse names | no comments.no comments. |
Refuse amount add | Added refuse | no comments. |
Yield name add | Added yield names | no comments. |
Yield amount add | Added yields | Includes yields added to already existing foods as well as new foods |
Food source add | Added food sources | no comments.no comments. |
Nutrient source add | Added nutrient sources | no comments.no comments. |
These files are in the same format as the principal or support files.
File name | File description | Comment |
---|---|---|
Food name change | Changed food names | no comments. |
Nutrient name change | Changed nutrient names | no comments. |
Nutrient amount change | Changed nutrient values | no comments. |
Measure name change | Changed measures | no comments. |
Conversion factor change | Changed conversion factor values | no comments. |
Yield name change | Changed yield names | no comments. |
Yield amount change | Changed yield values | no comments. |
Refuse name change | Changed refuse names | no comments. |
Refuse amount change | Changed refuse values | no comments. |
Food source change | Changed food source descriptions | no comments. |
If the values in any fields have changed, the entire record is included for that file. These files are in the same format as the principal or support files. Unfortunately, simple changes to the wording or spelling of the food names were not captured in the food change update file for this edition.
File name | File description | Comment |
---|---|---|
Food name delete | Deleted food names | no comments. |
Nutrient amount delete | Deleted nutrient values | Includes nutrient values that were removed from the database (including those which accompany a deleted food |
Conversion factor delete | Deleted conversion factor values | Includes conversion factors that accompany a deleted food (including those which accompany a deleted food |
Yield amount delete | Deleted yield values | no comments. |
Refuse amount delete | Deleted refuse values | no comments. |
Nutrient name delete | Deleted nutrient names | no comments. |
Update files tables
Add table name | Field name |
---|---|
Food name add | FoodID |
FoodCode | |
FoodDescription | |
FoodDescriptionF | |
FoodDateOfEntry | |
FoodDateOfPublication | |
FoodGroupID | |
FoodSourceID | |
CountryCode | |
ScientificName | |
Food source add | n/a |
Food group add | FoodGroupID |
FoodGroupCode | |
FoodGroupName | |
FoodGroupNameF | |
Nutrient amount add | FoodID |
NutrientNameID | |
NutrientValue | |
StandardError | |
NumberOfObservations | |
NutrientDateOfEntry | |
NutrientSourceID | |
Nutrient name add | NutrientNameID |
NutrientNameCode | |
NutrientSymbol | |
Unit | |
NutrientName | |
NutrientNameF | |
Tagname NutrientDecimals |
|
Nutrient source add | NutrientSourceID |
NutrientSourceCode | |
NutrientSourceDescription | |
NutrientSourceDescriptionF | |
Conversion factor add | FoodID |
MeasureID | |
ConversionFactorValue | |
ConvFactorDateOfEntry | |
Refuse amount add | FoodID |
RefuseID | |
RefuseAmount | |
RefuseDateOfEntry | |
Yield amount add | FoodID |
YieldID | |
YieldAmount | |
YieldDateOfEntry | |
Measure name add | MeasureID |
MeasureName | |
MeasureNameF | |
Refuse name add | RefuseID |
RefuseName | |
RefuseNameF | |
Yield name add | YieldID |
YieldName | |
YieldNameF |
Change table name | Field name |
---|---|
Food name change | FoodID |
FoodCode | |
FoodDescription | |
FoodDescriptionF | |
FoodDateOfEntry | |
FoodDateOfPublication | |
FoodGroupID | |
FoodSourceID | |
CountryCode | |
ScientificName | |
Food source change | FoodSourceID |
FoodSourceCode | |
FoodSourceDescription | |
FoodSourceDescriptionF | |
Food group change | n/a |
Nutrient amount change | FoodID |
NutrientNameID | |
NutrientValue | |
StandardError | |
NumberOfObservations | |
NutrientDateOfEntry | |
NutrientSourceID | |
Nutrient name change | NutrientNameID |
NutrientNameCode | |
NutrientSymbol | |
Unit | |
NutrientName | |
NutrientNameF | |
Tagname | |
NutrientDecimals | |
Nutrient source change | n/a |
Conversion factor change | FoodID |
MeasureID | |
ConversionFactorValue | |
ConvFactorDateOfEntry | |
Refuse amount change | FoodID |
RefuseID | |
RefuseAmount | |
RefuseDateOfEntry | |
Yield amount change | FoodID |
YieldID | |
YieldAmount | |
YieldDateOfEntry | |
Measure name change | MeasureID |
MeasureName | |
MeasureNameF | |
Refuse name change | RefuseID |
RefuseName | |
RefuseNameF | |
Yield name change | n/a |
Delete table name | Field name |
---|---|
7 Food name delete | FoodID |
FoodCode | |
FoodDescription | |
FoodDescriptionF | |
FoodDateEntry | |
FoodDateEnd | |
FoodGroupID | |
FoodSourceID | |
CountryCode | |
ScientificName | |
Food source delete | n/a |
Food group delete | n/a |
Nutrient amount delete | FoodID |
NutrientNameID | |
NutrientValue | |
StandardError | |
NumberOfObservations | |
NutrientDateEnd | |
Nutrient name delete | NutrientNameID |
NutrientNameCode | |
NutrientName | |
NutrientNameF | |
Nutrient source delete | NutrientSourceID |
NutrientSourceCode | |
NutrientSourceDescription | |
NutrientSourceDescriptionF | |
Conversion factor delete | FoodID |
MeasureID | |
ConversionFactorValue | |
ConvFactorDateEnd | |
Refuse amount delete | FoodID RefuseID |
RefuseAmount | |
RefuseDateOfEntry | |
Yield amount delete | FoodID YieldID |
YieldAmount | |
YieldDateOfEntry | |
Measure name delete | n/a |
Refuse name delete | n/a |
Yield name delete | n/a |
Although every attempt has been made to include all additions, changes and deletions to the CNF since the last release in these update files, the nature of this very large electronic database and the complex programming involved in editing, it is possible that some changes were not tracked accurately. It is recommended that one also downloads the entire database for reference.
Page details
- Date modified: