The MEDICATIONS table contains one entry for every medication being taken by our five patients. In this example, there is no single column that serves as a primary key for this table. As we can see in the following screenshot, this table includes information about the medication name, dose, frequency, route, prescribing physician, and prescription date. The NDC code of each medication is also included; we covered NDC codes in Chapter 2, Healthcare Foundations:

Including medications in our final table will not be straightforward. For example, the information in the tables does not indicate the class of each medication. The NDC code is present, but the NDC code is even more granular than the medication name since it includes the route of administration and dosage in making each unique code; therefore, multiple forms of lisinopril could have different NDC codes. In order to make a column for each medication, we could potentially separately make a table for each medication, which contains all of the medications that compose it, and then merge that information into our table.

If we choose to include dosage information, that field will require some cleaning. Notice that Patient #3 is receiving an anti-hypertensive combination drug–the valsartan component has a dosage of 160 mg, while the hydrochlorothiazide component has a dosage of 12.5 mg. This could possibly be coded as two separate drugs, but creating a script that splits combination drugs into two rows is not trivial.