ClinicalTrials.gov Intelligence: What the 50+ Table AACT Schema Actually Contains

ClinicalTrials.gov is not a simple spreadsheet. The AACT database mirrors the registry as a relational schema with more than 50 tables covering studies, sponsors, facilities, interventions, conditions, outcomes, eligibility, references, design groups, and many other entities. That richness is powerful, but it creates a join and normalization workload before trial intelligence can begin.

Core AACT tables

The STUDIES table is the anchor. SPONSORS and COLLABORATORS describe responsible parties. FACILITIES and FACILITY_CONTACTS provide site geography. INTERVENTIONS and DESIGN_GROUP_INTERVENTIONS connect treatments to arms. BROWSE_CONDITIONS and BROWSE_INTERVENTIONS provide MeSH terms. OUTCOME_MEASURES, REPORTED_EVENTS, and ELIGIBILITIES support protocol interpretation.

Sponsor normalization

Sponsor names are often inconsistent. A company may appear with abbreviations, subsidiaries, acquired names, or legal variants. Normalizing sponsors to ROR and parent hierarchies makes competitive trial activity comparable across time and therapeutic areas.

Geo-ready facility data

Facilities enable site selection, territory strategy, enrollment intelligence, and competitive activity monitoring. City, state, country, and coordinate fields need validation and standardization before they are useful in analytics tools.

Mapping trials to drugs

Intervention names are free text. Mapping them to RxNorm, ChEMBL, and the drug identity spine requires synonyms, brand/generic mappings, investigational names, and sometimes mechanism context. Once mapped, trial data can join to labels, approvals, safety signals, patents, and market data.

SELECT
  study.nct_id,
  study.brief_title,
  sponsor.normalized_sponsor_name,
  intervention.canonical_drug_id,
  facility.country,
  study.overall_status
FROM `tdd.clinical.studies` AS study
JOIN `tdd.clinical.sponsors` AS sponsor USING (nct_id)
LEFT JOIN `tdd.clinical.intervention_drug_bridge` AS intervention USING (nct_id)
LEFT JOIN `tdd.clinical.facilities` AS facility USING (nct_id)
WHERE study.phase IN ('Phase 2', 'Phase 3');

What trial intelligence adds

A query-ready trial product should expose sponsor hierarchy, intervention-drug identity, disease ontology terms, NIH funding flags, publication links, geographic site views, compliance scoring, and recruitment status history. These additions turn a registry mirror into competitive intelligence.

Insight: The value of AACT is not only the number of tables; it is the joins across sponsors, interventions, sites, conditions, publications, and product lifecycle data.

Related data products

• Clinical Trial Intelligence (ClinicalTrials.gov)
• Research Organization Registry (ROR)
• MeSH Disease & Topic Intelligence