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.
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)
Contact
If you require additional information regarding this data extract, contact:
Data structure for Licensed Natural Health 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) |
| Name | Type |
|---|---|
| product_id | number |
| purpose_e | varchar2(4000) |
| purpose_f | varchar2(4000) |
| 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) |
| 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) |
| 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) |
| 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) |
| Name | Type |
|---|---|
| product_id | number |
| route_type_desc | varchar2(120) |
| route_type_desc_f | varchar2(120) |
| Name | Type |
|---|---|
| product_id | number |
| proper_name | varchar2(200) |
| proper_name_f | varchar2(200) |
| common_name | varchar2(200) |
| common_name_f | varchar2(200) |
| Name | Type |
|---|---|
| product_id | number |
| product_name | varchar2(200) |
| primary_name | varchar2(1) |
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: