ARCHIVED - Read Me File

This content was archived on June 24 2013.

Archived Content

Information identified as archived on the Web is for reference, research or recordkeeping purposes. It has not been altered or updated after the date of archiving. Web pages that are archived on the Web are not subject to the Government of Canada Web Standards. As per the Communications Policy of the Government of Canada, you can request alternate formats on the "Contact Us" page.

(ARCHIVED - Text Version - 6 K)

The Licensed Natural Health Product Database (LNHPD) contains product specific information on natural health products that have been issued a product licence by Health Canada.

This extract contains both licensed and discontinued products - differentiated by the current status (found in the NHP_PRODUCTS file). Although the LNHPD is a relational database, there is a requirement to provide the data to users in a common format; therefore the data has been extracted into a flat file format. All files have the individual data fields enclosed in quotes and delimited by the bar ("|") character (code 124) found on most keyboards by doing Shift and backslash (\). All flat files for a particular licensed product are linked through a common PRODUCT_ID. An entity relationship diagram can be found following this page.

Company Information

In the LNHPD, each product has only the Applicant/Licensee associated with it. Each product will have a unique Natural Product Number (NPN) or Homeopathic Medicine Number (DIN-HM). A separate Company extract record is created for each product. If an Applicant/Licensee has more than one licensed product, it will appear as multiple extract records - each one with the unique NPN or DIN-HM linking it to the licensed product.

Licensed Products

The files included in the zipped file for licensed products are as follows:

NHP_products.txt: NHP Products
NHP_products_purpose.txt: NHP Products Purpose
NHP_prod_recommended_dose.txt: NHP Products Recommended Dose
NHP_products_risk.txt: NHP Products Risks
NHP_companies.txt: NHP Companies
NHP_medicinal_ingredients.txt: NHP Medicinal Ingredients
NHP_route.txt: NHP Routes
NHP_nonmedicinal_ingredients.txt: NHP Non-medicinal Ingredients
NHP_product_names.txt: NHP Product Names
All_NHP_files.txt: All NHP files

French Characters

French characters are being incorporated into the database. Currently the NHP_COMPANIES file is the only file that has French characters in it. The LNHPD database uses ASCII character set 8859. The codes for the commonly used characters are provided below:

  • 192 - À (accent grave)
  • 194 - Â (accent circonflexe) 
  • 199 - Ç (cedille)
  • 200 - È (accent grave)
  • 201 - É (accent aigu)
  • 202 - Ê (accent circonflexe)
  • 206 - Î (accent circonflexe)
  • 212 - Ô (accent circonflexe)
  • 217 - Ù (accent grave)
  • 219 - Û (accent circonflexe)


If you require additional information regarding this data extract, contact:

Data structure for Licensed Natural Health Products

Description NHP Products
Name Type
product_id number
licence_number varchar2(200)
product_name varchar2(200)
dosage_form varchar2(120)
dosage_form_f varchar2(120)
licence_date date
status varchar2(20)
status_f varchar2(20)
Description NHP Products Purpose
Name Type
product_id number
purpose_e varchar2(4000)
purpose_f varchar2(4000)
Description NHP Prod Recommended Dose
Name Type
product_id number
population_type_desc varchar2(120)
population_type_desc_f varchar2(120)
age number
age_minimum number
age_maximum number
uom_type_desc_age varchar2(120)
uom_type_desc_age_f varchar2(120)
quantity_dose number
quantity_minimum_dose number
quantity_maximum_dose number
uom_type_desc_quantity_dose varchar2(120)
uom_type_desc_quantity_dose_f varchar2(120)
frequency number
frequency_minimum number
frequency_maximum number
uom_type_desc_frequency varchar2(120)
uom_type_desc_frequency_f varchar2(120)
Description NHP Products Risk
Name Type
product_id number
risk_type_desc varchar2(120)
risk_type_desc_f varchar2(120)
sub_risk_type_desc varchar2(120)
sub_risk_type_desc_f varchar2(120)
risk_text_e varchar2(4000)
risk_text_f varchar2(4000)
Description NHP Companies
Name Type
product_id number
company_name varchar2(200)
company_address varchar2(120)
city varchar2(40)
province varchar2(40)
country varchar2(40)
postal_code varchar2(40)
Description NHP Medicinal Ingredients
Name Type
product_id number
proper_name varchar2(200)
proper_name_f varchar2(200)
common_name varchar2(200)
common_name_f varchar2(200)
potency_amount number
potency_unit_of_measure varchar2(120)
potency_unit_of_measure_f varchar2(120)
potency_constituent varchar2(120)
quantity number
quantity_minimum number
quantity_maximum number
quantity_unit_of_measure varchar2(120)
quantity_unit_of_measure_f varchar2(120)
ratio_numerator varchar2(10)
ratio_denominator varchar2(10)
dried_herb_equivalent varchar2(10)
dhe_unit_of_measure varchar2(120)
dhe_unit_of_measure_f varchar2(120)
extract_type_desc varchar2(120)
extract_type_desc_f varchar2(120)
source_material varchar2(120)
source_material_f varchar2(120)
Description NHP Route
Name Type
product_id number
route_type_desc varchar2(120)
route_type_desc_f varchar2(120)
Description NHP Nonmedicinal Ingredients
Name Type
product_id number
proper_name varchar2(200)
proper_name_f varchar2(200)
common_name varchar2(200)
common_name_f varchar2(200)
Description NHP Product Names
Name Type
product_id number
product_name varchar2(200)
primary_name varchar2(1)

LNHPD Extract Relationship Diagram

LNHPD Extract Relationship Diagram
LNHPD Extract Relationship Diagram

This extract relationship diagram shows how the following files; nhp_companies, nhp_medicinal_ingredients, nhp_product_names, nhp_products_purpose, nhp_route, nhp_non_medicinal_ingredients, nhp_prod_recommended_dose, and nhp_products_risk; are all linked through the Product_ID of the nhp_products file.

Page details

Date modified: