************************************************************************************************************************************************** * IDA2PM(Integrated Database for ADMET and Adverse effect predictive modeling Based on FDA Approved drugs data Schema Documentation: * Date: 10/09/2015 * Contact: ashenafi.legehar@helsinki.fi, leo.ghemtio@helsinki.fi **************************************************************************************************************************************************** ADMET: contains infomation on Absorpition, Distribution, Metabolisim and Toxicity information KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id character varying(6) NOT NULL Unique id for admet row absorption Text default NULL::character varying absorption information of the drug are stored in this column volume_of_distribution Text default NULL::character varying volume of distribution of the drug (eg. 1.16 L/kg) metabolism Text default NULL::character varying known metabolism of drug are stored in this colum route_of_elimination Text default NULL::character varying route of elimiation of a drug toxicity Text default NULL::character varying toxicity of a drug APPDOC: Document addresses or URLs to letters, labels, reviews, Consumer Information Sheets, FDA Talk Papers, and other types KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK appdocid character varying(6) NOT NULL unique id for appdoc appino character varying(4) NOT NULL appplication number of the drug seqno character varying(100) default NULL::character varying doctype character varying(100) default NULL::character varyin the type of document (eg. Letter, Review) doctitle character varying(200) default NULL::character varying the document title docurl character varying(200) default NULL::character varying link for the document docdate timestamp without time zone issue date actiontype character varying(10) default NULL::character varying Type of action which is taken (eg. AP (Approved), TP(Tentative approved) duplicatecounter character varying(2) duplicate counter if there is other similar document APPDOCTYPE_LOOKUP: Type of document that is linked, which relates to the AppDoc table KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK AppDocType character varying(50) NOT NULL Unique key for appdoctype_lookup table eq(Label ,Letter Other Important Information from FDA, Withdrawal Notice Withdrawal Notice etc. ) SortOrder integer NOT NULL used to sort the docment type APPLICATION: Application number and sponsor name KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK applno character varying(6) NOT NULL unique identifier for application entity appltype character varying(5) NOT NULL A=ANDA(Abbreviated New Drug Application), N=NDA(New Drug Application), B=BLA(Biologic License Application) sponsorapplicant character varying(50) NOT NULL company name mostrecentlabelavailableflag bytea NOT NULL currentpatentflag bytea NOT NULL actiontype character varying(10) NOT NULL Action taken by FDA (eg AP (Approved), TP (Tentative Approved)) chemical_type character varying(3) default NULL::character varying It refer chemical_look up table (eq. 1 = New molecular entity (NME), 2=New active ingredient, 3=New dosage form 4=New combination etc.) therapeutic_potential character varying(2) default NULL::character varying therapeutic potential of the drug (label as: P =A drug that appears to represent an advance over available therapy Priority review drug or S=A drug that appears to have therapeutic qualities similar to those of an already marketed drug Standard review drug) orphan_code character varying(1) default NULL::character varying orphan code which is lable as "V "(orphan drug) APPLICATION_CHEMICAL_TYPE_LOOKUP: Association table (Application->ChemicalTypeLookup) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for application_chemical_type_lookup entity FK chemicaltype_id character varying(3) NOT NULL Foriegn key to chemical_type entity FK application_id character varying(6) NOT NULL Foriegn key to application entity APPLICATION_REGACTIONDATE: KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for application_regactiondate FK RegActionDate_id integer NOT NULL Foriegn key to RegActionDate FK application_id character varying(6) NOT NULL Foriegn key to Application APPLICATION_PRODUCT: Association table (Application-Product) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for application_product entity FK applno_id character varying(6) NOT NULL Foriegn key to application entity FK product_id character varying(6) NOT NULL Forien key to product entity APPLICATION_REVIEW_CLASS_LOOKUP: Association table (Application->ReviewClassLookup) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifer for application_review_class_lookup FK ReviewClass_id integer NOT NULL Foriegn key to Reviewclass entity FK application_id character varying(6) NOT NULL Foriegn key to application entity CHEMICALTYPE_LOOKUP: This table include the chemical type code and description KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK ChemicalTypeID character varying(3) NOT NULL unique identifier for chemicaltype_lookup entity ChemicalTypeCode character varying(3) default NULL::character varying unique code for chemical type ChemicalTypeDescription character varying(120) NOT NULL chemical type description of the drug( New molecular entity (NME), New active ingredient,New formulation or new manufacturer etc.) DOCTYPE_LOOKUP: Supplement type code and description to the application number. KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK DocType character varying NOT NULL Document type in Abbreviation DocTypeDesc character varying(50) default NULL::character varying B = Biopharmaceutics N = Approval, SCI= Chemistry in Effect , TP= Tentative Approval, SSW= Supplement Withdrawal (MarkIV) etc. DRUG_AREA: This table include drug area and prescription area of a drug KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for drug_area FK product_id character varying(7) NOT NULL Foriegn key to product entity drug_area character varying(50) NOT NULL Area of a drug acting or adminstered prescription_drug_area character varying(400) prescription area of the drug DRUG_REPORTED: This table contains drug_characterization, dosage, indication and action taken on the drug KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for drug_reported entity drug_characterization integer NOT NULL Reported role of drug in adverse event. Possible Codes are: 1= suspect 2= concomitant 3= interacting drug_dosage_text character varying(400) default NULL::character varying Text describing drug dosage and frequency drug_indication character varying(400) default NULL::character varying MedDRA Preferred Term (text string) is used to characterize indication for use drug_start_date character varying(10) default NULL::character varying Date when patient started taking the kth drug drug_end_date character varying(10) default NULL::character varying Date when patient stoped taking the kth drug action_drug character varying(10) default NULL::character varying Actions taken with drug – Possible codes are:1= Drug Withdrawn 2= Dose reduced 3= Dose Increased 4= Dose not changed 5= Unknown 6= Not applicable FK adverse_id integer NOT NULL applno character varying(50) EXTERNAL_REFERECENCE_FOR_LIGAND: This table contains exteranal id of the ligand/active ingredient such as Ligand HET ID in PDB, PubChem CID, PubChem SID, DrugBank ID of Ligand etc KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for external_reference for ligand Ligand HET ID in PDB character varying(6) default NULL::character varying HET Id of a compound in PDB database PubChem CID character varying(10) default NULL::character varying compund id in PubChem database PubChem SID integer Substance id in PubChem SID ChEBI ID of Ligand character varying(10) default NULL::character varying compund idenfiier in ChEBI database DrugBank ID of Ligand character varying(50) default NULL::character varying Compund identifier in Drugbank database IUPHAR_GRAC ID of Ligand character varying(10) default NULL::character varying Compund identifier in IUPHAR_GRAC KEGG ID of Ligand character varying(10) default NULL::character varying Compund identifier in KEGG ZINC ID of Ligand character varying(20) default NULL::character varying Compund identifier in ZINC ADVERSE_EFFECT: Contains information adverse effect of a drug which is reported on FDA AERS reporting system KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL report_id integer NOT NULL Unique report identifier used by the FDA AERS system. This number is used to any additional information primary_source_country character varying(5) default NULL::character varying Country of the primary reporter transmission_date timestamp without time zone Date format code; 102 means CCYYMMDD, where CCYY is the 4-digit year reactionmeddraversionpt character varying(10) default NULL::character varying MedDRA version for reaction/event term PT adverse_effect character varying(150) default NULL::character varying MedDRA Preferred Term (text string) is used to characterize the event(s) reaction_out_come character varying(5) default NULL::character varying Outcome of reaction/event at the time of last observation Possible codes are: 1= recovered/resolved 2= recovering/resolving 3= not recovered/not resolved 4= recovered/resolved with sequelae 5= fatal 6= unknown medical_product_t character varying(500) default NULL::character varying LIGAND_TARGET_INTERACTION: This table contains Binding Affinity of ligand such as ki_nm , ic50_nm KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for the ligand_target_interaction entity ki_nm character varying(50) default NULL::character varying biniding affinity ic50_nm character varying(50) default NULL::character varying half maximal inhibitory concentration kd_nm character varying(10) default NULL::character varying dissociation constant ec50_nm character varying(10) default NULL::character varying half maximal effective concentration kon_m_1_s_1 character varying(10) default NULL::character varying koff_s_1 character varying(10) default NULL::character varying ph character varying(5) default NULL::character varying pH of a compund temp character varying(10) default NULL::character varying Temperature inchkey character varying(27) default NULL::character varying FK target_id integer Not Null Foriegn key to target pdb_id_for_ligand_target_complex text default NULL::character varying Ligand-Target complex strucure idenitifier in PDB LIGAND_TARGET_INTERACTION_STRUCTURE: Association table (Ligand_target_interaction->Structure) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL Unique identifier for Ligand_target_interaction_structure entity FK ligand_tar_int_id integer NOT NULL Foriegn key to ligand_target interaction entity FK structure_id integer NOT NULL Foriegn key to strucure entity MEDICAL_PRODUCT: This table include Drug which is reported in FDA AERS reporting system KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL medical_product character varying(300) default NULL::character varying Valid Trade Name if populated;otherwise, verbatim name used by reporter medical_product_s character varying(150) default NULL::character varying Valid Trade Name if populated;otherwise, verbatim name used by reporter medical_product_t character varying(400) default NULL::character varying Valid Trade Name if populated;otherwise, verbatim name used by reporter FK adverse_id integer NOT NULL Foriegn key to adverse effect entity PATIENT_INFO: This table contains patient age, sex and weight KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier pattient_info patient_age character varying(15) default NULL::character varying Value for patient age at onset of adverse event. patient_age_unit character varying(10) default NULL::character varying Units for the age value patient_sex character varying Gender indicator Possible codes are: 0= unknown 1= male 2= female 9= unspecified weight character varying Weight in kilograms. NOTE: Value will display with up to 5 numeric digits with a max of 2 decimal points FK adverse_id integer Foriegn key to adverse_effect entity PHYSICO_CHEMICAL: This table contains logp, logs, molecular weight, refractivity, rotatable_bond_count, hydrogen_bond_acceptor etc. KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id character varying(8) NOT NULL unique identifier for physico_chemical logp_alogps numeric calulated logp values by http://www.vcclab.org/lab/alogps/ logs numeric calculated logs logp_chemaxon numeric calculated logP by chemaxon molecular_weight numeric Molecular Weight of a compund polar_surface_area numeric polar surface area (Å2) refractivity numeric refractivity polarizablity numeric polarizablity rotatable_bond_count integer Rotatable bond count hydrogen_bond_acceptor integer Hydrogen bond Acceptor hydrogenbond_donor integer Hydorgen bond Donor pka_acidic numeric pKa acidic pka_basic numeric pKa basic physiological_charge integer Physicological charge PRODUCT: This table contains the products included in each application. Includes form, dosage, and route. KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id character varying(6) NOT NULL unique identifier for product entity appl_no character varying(6) NOT NULL application number productno character varying(3) NOT NULL product number form character varying(255) default NULL::character varying form of administerd(eg. TABLET;ORAL) dosage character varying(240) default NULL::character varying dosage of the drug procuctmtktstatus smallint NOT NULL 1=prescription, 2=OTC, 3=discontinued, 4=tentative approval tecode character varying(100) default NULL::character varying For More info read:http://www.fda.gov/Drugs/DevelopmentApprovalProcess/ucm079068.htm#TEC referencedrug bytea 0=not RLD, 1=RLD, 2=TBD drugname character varying(125) default NULL::character varying brand name ->a drug marketed under a proprietary, trademark-protected name activeingred character varying(255) default NULL::character varying component that provides pharmacological activity or other direct effect in the diagnosis, cure, mitigation, treatment, or prevention of disease PRODCUT_ADMET: Association table (Prodcut-ADMET) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for product_admet entity FK admet_id character varying(6) NOT NULL Foriegn key to admet entity FK product_id character varying(6) NOT NULL Foriegn key to product entity PRODUCT_ADVERSE_EFFECT: Association table (product->AdverseEffect) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for product_adverse_effect FK product_id character varying(6) NOT NULL Foriegn key to product entity FK adverse_effect_id integer NOT NULL Foriegn key to adverse_effect PRODUCT_EXTERNAL_REFERENCE: Association table (Prodcut-External Reference) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for Product_external_referece FK ProductID character varying(6) NOT NULL Foriegn key to product entity FK ExternalID integer NOT NULL Foriegn key to External referece entity PRODUCT_PRODUCTTEC_CODE: Association table (Product->ProductTecCode) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION FK product_id character varying(6) NOT NULL Foriegn key to product entity FK pro_tec_id character varying(6) NOT NULL Foriegn key to productTec_code entity PRODUCT_STRUCTURE: Association table (product->Structure) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for product_structure FK structure_id integer NOT NULL Foriegn key to Strucure entity FK product_id character varying(10) NOT NULL Foriegn key to Product entity PRODUCT_TECODE: Therapeutic Equivalence Code for Products KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id character varying(6) NOT NULL unique identifier for product_tecode ApplNo character varying NOT NULL application number of the drug ProductNo character varying(3) NOT NULL product number of the drug TECode character varying(50) NOT NULL Therapeutic Equivalence Code (eg . there are no known or suspected bioequivalence problems. These are designated AA, AN, AO, AP, or AT, depending on the dosage form actual or potential bioequivalence problems have been resolved with adequate in vivo and/or in vitro evidence supporting bioequivalence. These are designated AB) TESequence integer NOT NULL ProdMktStatus smallint NOT NULL product market status (1=prescription, 2=OTC, 3=discontinued, 4=tentative approval) REFERENCE_TARGET: This table contains external id for target and data souce KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for reference_target entity data_source character varying(200) default NULL::character varying source of target information pmid character varying(20) default NULL::character varying pubmed identifier FK target_id integer NOT NULL Foriegn key to target entity REG_ACTION_DATE_APPDOC: Association table (RegActionDate->Appdoc) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for reg_action_date_appdoc entity FK RegActionDate_id integer NOT NULL Foriegn key to RegActionDate entity FK AppdocID integer NOT NULL Foriegn key to AppDoc entity REGACTION_DATE: Approval history for each application. Includes supplement number and dates of approval KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for Regaction_date entity ApplNo character varying(6) NOT NULL application number ActionType character varying(10) NOT NULL action taken by FDA (AP= Approved ) InDocTypeSeqNo integer NOT NULL DublicateCounter integer NOT NULL ActionDate timestamp without time zone date of appoved by FDA DocType character varying(4) default NULL::character varying abbrivation for document type REVIEWCLASS_LOOKUP: This table includes reviewCode, LongDescription and shortDescription KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK ReviewClassID integer NOT NULL unique identifier for Reveiewclass_lookup ReviewCode character varying(1) NOT NULL Document review codes are P,S,V LongDescription character varying(100) NOT NULL P=A drug that appears to represent an advance over available therapy , S= A drug that appears to have therapeutic qualities similar to those of an already marketed drug, V = Orphan drug shortDescription character varying(20) P= Priority review drug, S=Standard review drug, V=Orphan drug STRUCTURE: conatins structure information of a compound KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for sturcure molecular_formula character varying(50) default NULL::character varying moleculer formula of a compund smile character varying(1800) default NULL::character varying Strucure of a compund in SMILE (simplified molecular-input line-entry system) inchi text default NULL::character var id Structure of a compund in InchI(IUPAC International Chemical Identifier) inchkey character varying(100) default NULL::character varying compund identifier STRUCTURE_PHYSICOCHEMICAL_N: Association table (Strucure->PhysicoChemical) KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for structure_physicochemical_n entity structure_id integer NOT NULL Foriegn key to Sturcture entity physico_id character varying(8) NOT NULL Foriegn key to phsico_chemical entity TARGET: Conatins drug target and source organism KEYS COLUMN_NAME DATA_TYPE MODIFIERS DESCRIPTION PK id integer NOT NULL unique identifier for target table target_name character varying(200) default NULL::character varying Drug target name target_source_organism character varying(200) default NULL::character varying Target source organism target_chain_sequence text default NULL::character varying Target chain sequence