Read Me file March 8 2010 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. Copmany 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 OFrench 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 - Ç (cédille) * 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: nhpd_dpsn@hc-sc.gc.ca 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 Non-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) Description NHP Product Names Name Type product_id number product_name varchar2(200) primary_name varchar2(1) LNHPD Extract Relationship Diagram NHP_COMPANIES ------------------------------------------+ | NHP_MEDICINAL_INGREDIENTS ---------------------------+ | | | NHP_PRODUCT_NAMES --------------------------------+ | | | | | NHP_PRODUCTS_PURPOSE --------------------------+ | | | | | | | NHP_PRODUCTS | | | | NHP_ROUTE -------------------------------------+ | | | | | | NHP_NON_MEDICINAL_INGREDIENTS --------------------+ | | | | NHP_PROD_RECOMMENDED_DOSE ---------------------------+ | | NHP_PRODUCTS_RISK --------------------------------------+