Schema Reference

This page documents tables and columns in the orangebook.db export. CSV files will mimic the relational tables in orangebook.db. The dataset is organized into four layers: core FDA snapshot tables (applications, products, patents, exclusivity, drug labels), reference lookup tables (exclusivity codes, patent use codes), derived analytical views (LOE estimates, first generic entry), and the monthly change log tables. For definitive definitions of FDA terms, please see the FDA Orangebook Data Files Reference

applications #

One row per FDA drug application. The Orange Book covers small-molecule drugs only: NDA (brand) and ANDA (generic). Biologics are tracked in the FDA Purple Book and are not present here. The application number (appl_no) is the primary join key across this dataset (make sure you don’t confuse application numbers).

Company search pattern: Use sponsor_name LIKE '%PFIZER%' for raw FDA name searches (always populated). Where available, you can use rxdl_company LIKE '%PFIZER%' for harmonized searches which will consolidate subsidiaries and name account for name changes. Coverage of rxdl_* fields skews toward commercially significant NDAs from public companies that have active clinical trials. Most ANDA filers are private generic manufacturers and remain unmatched.

products #

One row per product formulation. A single NDA routinely has multiple products — different strengths, routes, and release types. An NDA for a 10 mg, 20 mg, and 40 mg tablet will have three rows here (possibly four if an IV form was also approved). Patents and exclusivity link at the (appl_no, product_no) level.

Key join pattern: When you want one row per NDA (not one per strength), filter AND p.reference_listed_drug = 'Yes'. When you need full NDA coverage (including pre-2000 drugs with no RLD), do not make this a required filter.

patents #

Active patent listings in the Orange Book. Each row represents one patent listed against one specific product formulation. The same patent number can appear multiple times if it covers multiple formulations.

Primary key note: The natural key is (appl_no, product_no, patent_no, use_code, pediatric_excl) — the same patent number can appear as two distinct rows (with and without pediatric exclusivity), representing different legal IP positions.

patent_details #

USPTO metadata for each listed patent number, sourced from PatentsView. One row per unique patent number.

exclusivity #

FDA statutory exclusivity grants. Multiple exclusivity types can apply to the same product simultaneously.

PC code represents a Paragraph IV first-filer exclusivity: The PC code (definition: “PATENT CHALLENGE”) is granted to the first ANDA applicant that files a Paragraph IV certification asserting that a listed Orange Book patent is invalid or will not be infringed. If the challenge succeeds, the first filer earns 180 days of exclusive generic marketing before other ANDAs can launch. PC entries appear on ANDA appl_no values, not NDA values, so they do not appear in loe_estimates (which covers NDAs only). See the Exclusivity Types page for full details.

exclusivity_codes #

Reference table. Plain-English definitions for all exclusivity codes used in the exclusivity table.

-- Look up a code definition
SELECT exclusivity_code, definition FROM exclusivity_codes WHERE exclusivity_code = 'NCE';
-- Returns: NCE | NEW CHEMICAL ENTITY

-- See all codes in the dataset
SELECT exclusivity_code, definition FROM exclusivity_codes ORDER BY exclusivity_code;

patent_use_codes #

Reference table. Definitions for FDA use codes assigned to method-of-use patents in the patents table. Over 4,000 codes are currently defined.

Use codes are important for Paragraph IV challenges and section viii statements for ANDA applicants, and are meant to ensure ANDA applicants can understand exactly what the claimed patent is covering to avoid infringement of (or challenge) patent protections. See the the Congressional Research Service publication on this topic for more information.

-- All use codes for a specific NDA (021196 = Xyrem / sodium oxybate, a drug with multiple method-of-use patents)
SELECT DISTINCT pt.patent_no, pt.use_code, puc.definition, pt.patent_expire_date
FROM patents pt
LEFT JOIN patent_use_codes puc ON puc.use_code = pt.use_code
WHERE pt.appl_no = '021196'
ORDER BY pt.patent_expire_date;

drug_labels #

One row per NDA application, containing the most recent FDA-approved label from Structured Product Labeling (SPL). ANDA (generic) applications have no independent label record — they reference the brand NDA.

Coverage: Approximately 81% of NDAs have indication text. Coverage is ~90%+ for 2010s and later approvals, ~30–50% for pre-2000 approvals. See Drug Labels for the full coverage table and ANDA join pattern.

loe_estimates (derived view) #

One row per NDA that currently has at least one active patent or exclusivity listing. This is the analytical core of the dataset — the computed loss-of-exclusivity date per NDA. NDAs that have already passed their reported LOE are excluded; use first_generic_entry for those.

Methodology: reported_loe_date = MAX(max_patent_exp, max_excl_exp). Pediatric exclusivity (+6 months) is applied per-patent before taking the MAX — the PED-adjusted expiry for each individual patent is computed first, then MAX is taken across all adjusted patent expiries. See LOE Estimates: Methodology for the full computation and caveats.

first_generic_entry (derived view) #

One row per NDA formulation (by ingredient + dosage_form_route). Identifies the earliest ANDA approval date for each brand drug formulation — the observed date when generic competition entered. Historical complement to loe_estimates.

Multiple rows per NDA: A single NDA can yield multiple rows — one per distinct dosage_form_route. Different formulations lose exclusivity independently. To collapse to one row per NDA, use MIN(first_generic_approval_date) GROUP BY nda_appl_no.

Effective exclusivity period: ROUND((julianday(first_generic_approval_date) - julianday(nda_approval_date)) / 365.25, 1) yields years the brand held the market. Can be negative when the NDA was filed for a molecule that already had generics — filter first_generic_approval_date >= nda_approval_date for genuine LOE analysis.

Change Log Tables #

Four tables track monthly changes derived from the Orange Book snapshot: product_changes, patent_changes, exclusivity_changes, and application_changes. All share this structural pattern:

beginning_of_history is not a genuine addition. Rows with this event were already present when RxDataLab first imported the dataset (October 2023 in the current export). A drug approved in 1995 that was in the October 2023 snapshot gets beginning_of_history — it is not a “new approval” in 2023. For trend analysis, always filter: WHERE event != 'beginning_of_history'.

updated events show new values only. To recover what changed (the before/after), self-join to the most recent prior event for the same record key using the id column. See Change History: updated events.

product_changes additionally includes an appl_type column ("NDA" or "ANDA") so you can filter to a specific application type without joining to applications.

export_metadata #

Single-row table with information about when this export was generated and what history it covers.

SELECT * FROM export_metadata;

Three Application Number Types #

Three distinct number types appear in the dataset, and two of them contain the word “application”. Be careful when querying that you are using appropriate values:

appl_no is the join key across all FDA tables. patent_application_number is a completely separate identifier used only for USPTO prosecution research. When writing joins, always qualify with table aliases (pt.appl_no for the FDA drug application, pd.patent_application_number for the USPTO filing).