Pillbox Engine data dictionary

Values supplied by the drug companies to FDA as part of the Structured Product Labels are labeled [SPL]. All other values are added by the National Library of Medicine’s (NLM) Pillbox or RxNorm datasets.

ID varchar(128) Unique identification assigned to each record by the engine.
Enabled varchar(128)
created_at varchar(128) Date record was created in the engine
updated_at varchar(128) Date record was last updated by the engine
spp varchar(128) SETID-product code-part number – the three values needed to uniquely identify a product.
setid varchar(128) [SPL] SetId
splsize varchar(128) [SPL] size of the pill in millimeters
pillbox_size varchar(128) If NLM has an image of the pill and the size is different from the value given by the firm, the NLM value in millimeters goes here.
splshape varchar(128) [SPL] The coded value describing the shape of the pill
splshape_text varchar(128) [SPL] The free text value describing the shape of the pill
pillbox_shape_text varchar(128) If NLM has an image of the pill and the shape is different from the value given by the firm, the NLM value goes here.
splscore varchar(128) [SPL] Score value for the pill
pillbox_score varchar(128) If NLM has an image of the pill and the score is different from the value given by the firm, the NLM value goes here.
splimprint varchar(128) [SPL] Text appearing on the pill
pillbox_imprint varchar(128) If NLM has an image of the pill and the imprint is different from the value given by the firm, the NLM value goes here.
splcolor varchar(128) [SPL] Coded value(s) for the color(s) of the pill
splcolor_text varchar(128) [SPL] Color(s) of the pill
pillbox_color_text varchar(128) If NLM has an image of the pill and the color is different from the value given by the firm, the NLM value(s) goes here.
spl_strength varchar(2048) [SPL] Active ingredients and the dosage amounts
spl_ingredients varchar(2048) [SPL] Active ingredients in the pill
spl_inactive_ing varchar(2048) [SPL] Inactive ingredients in the pill
source varchar(128) [SPL] Prescription, over-the-counter, homeopathic, veterinarian, remainder
rxtty varchar(128) RXTTY value from RxNorm API based on product code
rxstring varchar(2048) RXTSRING value from RxNorm API based on product code
rxcui varchar(128) RxNorm Concept Unique Identifier value from RxNorm API based on product code
product_code varchar(128) [SPL] the pill’s product code
part_num varchar(128) [SPL] If the product is delivered in parts (ex: birth control multi-packs), the part number identifying the unique pill within the multi-pack
part_medicine_name varchar(2048) [SPL] If the product is delivered in parts (ex: birth control multi-packs), the name identifying the unique pill within the multi-pack
ndc9 varchar(128) [SPL] 9-digit form of the National Drug Code for this pill. Does not include the packaging value.
medicine_name varchar(2048) [SPL] Name of the medciation
marketing_act_code varchar(128) [SPL]
effective_time varchar(128) [SPL] Date the information in the label is effective
file_name varchar(128) [SPL] name of the xml file containing the label for the pill. Different from SetId
equal_product_code varchar(128) [SPL] Firms may use this value to indicate another firm’s product they are repackaging or relabeling
dosage_form varchar(128)
document_type varchar(128)
dea_schedule_code varchar(128) [SPL] Drug Enforcement Administration, Controlled Substance Schedule code for the pill, if the pill is scheduled
dea_schedule_name varchar(128) [SPL] Drug Enforcement Administration, Controlled Substance Schedule name for the pill, if the pill is scheduled
author_type varchar(128)
author varchar(2048) [SPL] company submitting the labeling information for this pill to FDA. This company may be the manufacturer or a repackager/relabeler of another company’s product
approval_code varchar(128)
image_source varchar(128) If there is an image, the agency/program which took the picture of the pill
splimage varchar(2048) If there is an image, its filename
has_image varchar(128) Legacy field indicating a record has an image
epc_match varchar(128) Indicates the pill is listed as being a repackaged/relabeled version of another product
version_number varchar(128) Not yet implemented
laberer_code varchar(128) Not yet implemented
application_number varchar(128) Not yet implemented
updated varchar(128) Date record was last updated
stale varchar(128) The pill no longer has an active label. The product is no longer being actively marketed.
new varchar(128) The pill was added to the database at the most recent data process
has_image_new int(1)_
from_sis int(1) Legacy field
no_rxcui int(1) Legacy field
spl_id int(8) Legacy field