Building First Databank on MySQL

The First Databank data ships with 2 primary pieces of data:

• DDL - The SQL required to build the tables
• DML - The SQL required to populate the tables

There are about 291 tables! Ee-gads! That's a lot of tables.

The DDL comes in a .ZIP file called "NDDF PLUS DDL.ZIP".
So the first thing you'll want to do is unzip this file. Once unzipped you will see a simple directory structure.

The directories are named consistent with functional areas of First Databank. When you see the names it will make more sense to you.
The DDL comes in two flavors:

1. ORACLE
2. ANSI

We will be populating a MySQL database so we will be interested in using the ANSI versions of the DDL.

Here is a shell script to populate the database. You'll notice at the top some environment/shell variables are assigned (DBNAME,DBUSERID,DBUSERPW,etc.) The names are not that important but the values are. Adjust the values to be consistent with your installation.

#!/bin/bash
echo "LOADING DDL"
DBNAME=firstdb
DBUSERID=fdb
DBUSERPW=fdb
OFILE=jed.log
CMD="mysql --user=${DBUSERID} --password=${DBUSERPW} ${DBNAME}"
echo "CMD======${CMD}"
${CMD} < "MinMax/MINMAX 2.0/MinMax Adult Daily Dose/MINMAX_ADULT_DAILY_DOSE_ANSI.SQL" > ${OFILE}
${CMD} < "MinMax/MINMAX 2.0/MinMax Adult Daily Range/MINMAX_ADULT_DAILY_RANGE_ANSI.SQL" > ${OFILE}
${CMD} < "MinMax/MINMAX 2.0/MinMax Geriatric Daily Dose/MINMAX_GERIATRIC_DAILY_DOSE_ANSI.SQL" > ${OFILE}
${CMD} < "MinMax/MINMAX 2.0/MinMax Geriatric Daily Range/MINMAX_GERIATRIC_DAILY_RANGE_ANSI.SQL" > ${OFILE}
${CMD} < "MinMax/MINMAX 2.0/Pediatric Dosing/PEDIATRIC_DOSING_ANSI.SQL" > ${OFILE}
${CMD} < "Precautions/PCAUTION 1.0/Geriatric Precautions/GERIATRIC_PRECAUTIONS_ANSI.SQL" > ${OFILE}
${CMD} < "Precautions/PCAUTION 1.0/Lactation Precautions/LACTATION_PRECAUTIONS_ANSI.SQL" > ${OFILE}
${CMD} < "Precautions/PCAUTION 1.0/Pediatric Precautions/PEDIATRIC_PRECAUTIONS_ANSI.SQL" > ${OFILE}
${CMD} < "Precautions/PCAUTION 1.0/Pregnancy Precautions/PREGNANCY_PRECAUTIONS_ANSI.SQL" > ${OFILE}
${CMD} < "Counseling Messages/CMM 1.0/COUNSELING_MESSAGES_ANSI.SQL" > ${OFILE}
${CMD} < "Dosage Range Check/DRCM 3.0/DOSAGE_RANGE_CHECK_ANSI.SQL" > ${OFILE}
${CMD} < "Drug Allergy/DAM 4.0/DRUG_ALLERGY_ANSI.SQL" > ${OFILE}
${CMD} < "Drug-Disease Contraindications/DDCM 2.0/DRUG_DISEASE_CONTRAINDICATIONS_ANSI.SQL" > ${OFILE}
${CMD} < "Drug-Drug Interaction/DDIM 3.3/DRUG_DRUG_INTERACTION_ANSI.SQL" > ${OFILE}
${CMD} < "Drug-Food Interaction/DFIM 1.0/DRUG_FOOD_INTERACTION_ANSI.SQL" > ${OFILE}
${CMD} < "Drug-Lab Interference/DLIM 2.0/DRUG_LAB_INTERFERENCE_ANSI.SQL" > ${OFILE}
${CMD} < "Duplicate Therapy/DPT 1.0/DUPLICATE_THERAPY_ANSI.SQL" > ${OFILE}
${CMD} < "Indications/INDM 2.0/INDICATIONS_ANSI.SQL" > ${OFILE}
${CMD} < "Intravenous/IVM 1.0/INTRAVENOUS_ANSI.SQL" > ${OFILE}
${CMD} < "NDDF Descriptive and Pricing/NDDF BASICS 3.0/NDDF_DESCRIPTIVE_BASICS_ANSI.SQL" > ${OFILE}
${CMD} < "NDDF Descriptive and Pricing/NDDF ETC 1.0/NDDF_DESCRIPTIVE_ENHANCED_THERAPEUTIC_CLASSIFICATION_ANSI.SQL" > ${OFILE}
${CMD} < "NDDF Descriptive and Pricing/NDDF FML 1.0/NDDF_DESCRIPTIVE_MEDICAL_LEXICON_ANSI.SQL" > ${OFILE}
${CMD} < "NDDF Descriptive and Pricing/NDDF MEDNAMES 3.0/NDDF_DESCRIPTIVE_MEDICATION_CONCEPTS_ANSI.SQL" > ${OFILE}
${CMD} < "NDDF Descriptive and Pricing/NDDF MTL 1.0/NDDF_DESCRIPTIVE_MEDICAL_TEST_LEXICON_ANSI.SQL" > ${OFILE}
${CMD} < "NDDF Descriptive and Pricing/NDDF XRF 1.0/NDDF_DESCRIPTIVE_CROSS_REFERENCE_ANSI.SQL" > ${OFILE}
${CMD} < "NDDF Descriptive and Pricing/TALL MAN PLUS 1.0/NDDF_DESCRIPTIVE_TALL_MAN_ANSI.SQL" > ${OFILE}
${CMD} < "Patient Education/PEM 2.0/PATIENT_EDUCATION_ANSI.SQL" > ${OFILE}
${CMD} < "Prescriber Order Entry/POEM 2.0/PRESCRIBER_ORDER_ENTRY_ANSI.SQL" > ${OFILE}
${CMD} < "Prioritized Label Warnings/LBLW 1.0/PRIORITIZED_LABEL_WARNINGS_ANSI.SQL" > ${OFILE}
${CMD} < "Side Effects/SIDE 2.0/SIDE_EFFECTS_ANSI.SQL" > ${OFILE}
echo "All done ${0}"