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.

Food code field structure and roles
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.

Description of files
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

File name: food name
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
refuse  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:

File name: nutrient  amount
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:

File name: conversion factor
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:

File name: refuse amount
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:

File name: yield amount
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

File name: food group
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:

File name: food source
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:

File name: nutrient name
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:

File name: nutrient source
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:

File name: measure name
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:

File name: refuse name
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:

File name: yield name
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.

A. Added files
File Name File description Comment
Food name add Added food names no comments.
Nutrient name add Added nutrient names 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.
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.
Nutrient source add Added nutrient sources no comments.

These files are in the same format as the principal or support files.

B. Changed files
File name File description Comment
Food name change Changed food names
Nutrient name change Changed nutrient names
Nutrient amount change Changed nutrient values
Measure name change Changed measures
Conversion factor change Changed conversion factor values
Yield name change Changed yield names
Yield amount change Changed yield values
Refuse name change Changed refuse names
Refuse amount change Changed refuse values
Food source change Changed food source descriptions

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.

C. Deleted files
File name File description Comment
Food name delete Deleted food names
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
Refuse amount delete Deleted refuse values
Nutrient name delete Deleted nutrient names

Update files tables

Added files
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
 
Changed files
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
 
Deleted files
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: