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
• New for this edition
• 2015 File relationships
  • Description of files
• Principal files
  • I. Food name file
  • II.Nutrient amount file
  • III.Conversion factor file
  • IV. Refuse amount file
  • V. Yield amount file
• Support files
  • A. Food group file
  • B. Food source file
  • C. Nutrient name file
  • D. Nutrient source file
  • E. Measure name file
  • F. Refuse name file
  • G. Yield name file
• Update files list
  • A. Added files
  • B. Changed files
  • C. Deleted files
• Update files tables
  • Added files
  • Changed files
  • Deleted files

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.

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

Principal files

I. Food name file:

N = numeric - # of characters / # of decimals (if decimal)
T = text
D = date
n/a = not applicable

II. Nutrient amount file:

III. Conversion factor file:

IV. Refuse amount file:

V. Yield amount file:

Support files:

A. Food group file:

N = numeric - # of characters / # of decimals (if decimal)
T = text
D = date
n/a = not applicable

B. Food source file:

C. Nutrient name file:

D. Nutrient source file:

E. Measure name file:

F. Refuse name file:

G. Yield name file:

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.

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

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.

Update files tables

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.