User Tools

Site Tools


en:manuals:drugswidget:drugsdatabaseschema

FreeRx drugs database structure

This page is written primarily for developers. FreeRx is programmed to manage multiple therapeutic databases. To add a data source, you just need to create a database meeting the criteria described below with SQLite.

Drugs database schema has changed frequently between releases of FreeDiams. This page describes database schema version 0.9.0, with DDI (drug drug interactions), released on 2014-03-16.

Drugs database

Name of the Drugs database file

All drugs databases must use the above schema for their filename:

  • No specific suffix
  • 'drugs' filename forbidden
  • Mandatory extension : db
  • Example : drugs-usa.db

Tables of the database

The database should contain several tables linked together through primary keys.
There is one for each drug unique identifier number (UID). This unique identifier number may have a special name:

  • CIS (Code Identifiant de Spécialité) for France
  • NDA (New Drug Application) for USA (currently used)
  • NDC (National Drug Code) (not currently used)
  • PZN for Germany

This designation can be completed in the table INFORMATIONS.

The various tables are:

  • [t0] MASTER:
  • [t1] SOURCES: informations about the database and its source
  • [t2] DRUGS: contains the “FreeRx UID”, the “Drug Name(s)” and information on marketing;
  • [t3] MOLS:
  • [t4] COMPO: Composition, contains a description of various molecules to the drug
  • [t5] DRUG_FORMS:
  • [t6] DRUG_ROUTES:
  • [t7] ROUTES:
  • [t8] DRUG_SPC:
  • [t9] SPC_CONTENT:
  • [t10] SPC_CONTENTRESOURCE:
  • [t11] SPC_CONTENTRESOURCE_LINK:
  • [t12] SEARCHENGINES:
  • [t13] LABELS:
  • [t14] LABELSLINK:
  • [t15] PACKAGING: contains the format of treatment (packaging)
  • [t16] BIB:
  • [t17] BIB_LINK:
  • [t18] UNITS:
  • [t19] ATC:
  • [t20] ATC_LABELS:
  • [t21] ATC_CLASS_TREE
  • [t22] LK_MOL_ATC: contains links between code molecules and ATC codes (ids)
  • [t23] INTERACTIONS
  • [t24] IAKNOWLEDGE
  • [t25] IA_IAK
  • [t26] PIM_SOURCES
  • [t27] PIM_TYPES
  • [t28] PIMS
  • [t29] PIMS_RELATED_ATC
  • [t30] PIMS_RELATED_ICD
  • [t31] VERSION
  • [t32] CURRENTVERSION

[t0] Table_MASTER

  • DID: Drug UID, unique identifier used inside our database
  • UID: official drug UID (eg NDA for USA, CIS for France)

FDA: Abraxane (Pcalitaxel Protein-Bound Particles) Injectable Suspension (example)

f1f2f3f4f5f6
DIDUID1UID2UID3OLDUIDSID
80021660001 0216600011
key1FDA Application No. (NDA)sNDA or product numberØNDA+sNDA?

ANSM: ABELCET 5 mg/ml, suspension à diluer pour perfusion (66207341) (example)

f1f2f3f4f5f6
DIDUID1UID2UID3OLDUIDSID
1066207341NULLNULL662073411
key1Code CISØØCode CIS?

[t1] Table_SOURCES

ANSM

f1f2f3f4f5f6f7f8f9f10f11f12f13f14f15f16f17f18f19f20f21f22f23
SIDDBUIDMASTERLIDLANGWEBCOPYRIGHTLICENSEDATEDRUGS_VERSIONAUTHORSVERSIONPROVIDERWEBLINKDRUGUID_NAMEATCINTERACTIONSCOMPL_WEBSITEPACKUID_NAMECOMPLETIONAUTHOR_COMMENTSDRUGNAMECONSTRUCTORFMFCOMPATOPENREACT_COMPAT
1FR_AFSSAPS1fr_FRhttp://afssaps-prd.afssaps.fr/php/ecodex/index.phpNonFree 2013-06-04T17:11:580.8.4Eric Maeker, MD (Fr)0.8.4AFSSAPS2http://afssaps-prd.afssaps.fr/php/ecodex/index.phpCIS11 CIP 0.8.4

[t2] DRUGS table

  • VALID: is drug currently authorized? 0/1
  • MARKET: is drug currently marketed? 0/1
  • LINK_SPC: URL pointing to official documentation about the product

ABRAXANE exemple

f1f2f3f4f5f6f7f8f9f10f11
IDDIDSIDNAMEATC_IDSTRENGTHVALIDMARKETAID_MASTER_LIDLINK_SPCEXTRA_XML
80801ABRAXANEØ100MG/VIAL10238ØØ
key1key1?drug name(s)Østrength???ØØ

ANSM: ABELCET 5 mg/ml, suspension à diluer pour perfusion (66207341) (example)

f1f2f3f4f5f6f7f8f9f10f11
IDDIDSIDNAMEATC_IDSTRENGTHVALIDMARKETAID_MASTER_LIDLINK_SPCEXTRA_XML
10101ABELCET 5 mg/ml, suspension à diluer pour perfusionØØ1198http://afssaps-prd.afssaps.fr/php/ecodex/rcp/R0218720.htmØ
key1key1?drug name(s)Østrength???ØØ

[t3] MOLS

  • MID: Molecule ID
  • SID: ? (Substance ID) Value set to 1 in each row on DDI database version 0.9.0

FDA ABRAXANE (example)

f1f2f3f4
MIDSIDNAMEWWW
14111PACLITAXELØ
key2?Active Ingredient(s)Ø

ANSM ABELCET 5 mg/ml, suspension à diluer pour perfusion (66207341) (example)

f1f2f3f4
MIDSIDNAMEWWW
3721AMPHOTÉRICINE BØ
key2?Active Ingredient(s)Ø

[t4] COMPO

The COMPOSITION table

A drug can contain multiple molecules. One molecule can be present in a specific form and be metabolized into another (see NATURE / LK_NATURE).

Schema:

  1. DID: Drug ID, unique ID of the drug
  2. MID: Molecule ID, unique identifier of the molecule
  3. STRENGTH: molecule amount (per amount of form) e.g. “325 mg”
  4. STRENGTH_NID
  5. DOSE_REF : reference amount of form e.g. “1 tablet”, “2 mL suspension” ( form of the drug (redundant with DRUGS.FORM)
  6. REF_NID:
  7. NATURE : must be one of two kinds :
    • SA (from French Substance Active: English: Active ingredient) (can be used as default value) : name of Active substance
    • FT (from French Fraction Thérapeutique) component of the active ingredient that carries the pharmacological activity
  8. LK_NATURE: Link from SA to FT. Linked SA and FT share the same integer.

FDA ABRAXAN

f1f2f3f4f5f6f7f8f9
IDDIDMIDSTRENGTHSTRENGTH_NIDDOSE_REFREF_NIDNATURELK_NATURE
89801411100MG/VIALØØØSA1
89key1key2strengthØØØSA1

ANSM ABELCET 5 mg/ml, suspension à diluer pour perfusion (66207341) (example)

f1f2f3f4f5f6f7f8f9
IDDIDMIDSTRENGTHSTRENGTH_NIDDOSE_REFREF_NIDNATURELK_NATURE
2310372100,00 mgØun flaconØSA1
keykey1key2strengthØØØSA1

AMOXICILLINE/ACIDE CLAVULANIQUE BIOGARAN 500 mg/62,5 mg ADULTES (60403714) (example)

f1f2f3f4f5f6f7f8f9
IDDIDMIDSTRENGTHSTRENGTH_NIDDOSE_REFREF_NIDNATURELK_NATURE
320815795262,5 mg un comprimé FT2
320915791580 SA2
32101579387 SA1
32111579384500 mg un comprimé FT1

[t5] DRUG_FORMS

FDA ABRAXAN

f1f2
DIDMASTERLID

ANSM ABELCET 5 mg/ml, suspension à diluer pour perfusion (66207341) (example)

f1f2
DIDMASTERLID
1099

[t6] DRUG_ROUTES

FDA ABRAXAN

f1f2
DIDRID

ANSM ABELCET 5 mg/ml, suspension à diluer pour perfusion (66207341) (example)

f1f2
DIDRID
1042

[t7] ROUTES

FDA ABRAXAN

f1f2f3
RIDMASTERLIDSYSTEMIC

ANSM ABELCET 5 mg/ml, suspension à diluer pour perfusion (66207341) (example)

f1f2f3
RIDMASTERLIDSYSTEMIC
4243yes

[t8] DRUG_SPC

f1f2
DIDSPC_CONTENT_ID
f1f2
DIDSPC_CONTENT_ID

[t9] SPC_CONTENT

f1f2f3f4f5f6
IDLABELURL_SOURCEDATEOFDOWNLOADHTMLCONTENTSPCCONTENT_RESOURCES_LINK_ID
f1f2f3f4f5f6
IDLABELURL_SOURCEDATEOFDOWNLOADHTMLCONTENTSPCCONTENT_RESOURCES_LINK_ID

[t10] SPC_CONTENTRESOURCE

f1f2f3f4
IDTYPENAMECONTENT
f1f2f3f4
IDTYPENAMECONTENT
f1f2
RESOURCES_LINK_IDSPCCONTENTRESOURCES_ID

[t12] SEARCHENGINES

  • 0.9.0 DDI: empty table

[t13] LABELS

  • LID: label ID
  • LANG: language of the text
  • LABEL: text of the label

text snippets:

  • route of administration
  • dosage form
  • administrative information (authorization)
  • ddi texts: type of ddi, mechanism, management
f1f2f3
LIDLANGLABEL
keyISO language codetext
40engastro-enteral
41frgastro-entérale
42degastroenteral
f1f2
MASTERLIDLID

[t15] PACKAGING

f1f2f3f4f5f6f7f8
DIDSIDPACK_UIDLABELSTATUSMARKETDATEOPTION_CODE

[t16] BIB

f1f2f3f4f5f6f7
BIBIDTYPELINKTEXTREFABSTRACTEXPLAINXML
f1f2
MASTERIDBIBID

[t18] UNITS

f1f2
NIDVALUE

[t19] ATC

f1f2f3
ID CODE WARNDUPLICATES

[t20] ATC_LABELS

f1f2
ATCID MASTERLID

[t21] ATC_CLASS_TREE

f1f2f3f4
IDID_CLASSID_ATCBIBMASTERID

[t22] LK_MOL_ATC

f1f2f3
MIDATC_IDATC_SID

[t23] INTERACTIONS

[t24] IAKNOWLEDGE

[t25] IA_IAK

[t26] PIM_SOURCES

[t27] PIM_TYPES

[t28] PIMS

[t31] VERSION

[t32] CURRENTVERSION

Only one row: version of database

f1f2
1 0.9.0

Schema

  • UID : Unique Identifier for Drug, typically per-country
  • NAME : Commercial, “brand” name of the drug
  • FORM : Form of the drug (e.g. tablet, capsule, powder, solution, cream, soap bar)
  • ROUTE : route of administration (e.g. oral, intravenous, ophthalmic, rectal, topical)
  • ATC : drug's Anatomical Therapeutic Chemical classification system code
  • GLOBAL_STRENGTH : contains the global strength of the drug. Only used for the drugs name construction. See also: INFORMATIONS.DRUGS_NAME_CONSTRUCTOR.
    • Ex : 2.5mg;6.5mg
  • TYPE_MP (optional) : marketing permission (AMM in French, Schedule in CA)
  • AUTHORIZATION (optional) : type of authorisation
  • MARKETED : Boolean :: Yes = 1 ; No = 0
  • LINK_SPC (optional) : internet link to a “summary of product characteristics”

The UID must be unique an persistent in time. To ensure that UID is unique, use this SQL command:

SELECT COUNT(*), uid, name FROM drugs GROUP BY uid HAVING COUNT(*) > 1 ORDER BY name ASC

The COMPOSITION table

A drug can contain multiple molecules. One molecule can be present in a specific form and be metabolized into another (see NATURE / LK_NATURE).

Schema:

  • UID : UID of the drug
  • MOLECULE_FORM : form of the drug (redundant with DRUGS.FORM)
  • MOLECULE_CODE : unique identifier of the molecule
  • MOLECULE_NAME : name of the molecule
  • DOSAGE : molecule amount (per amount of form) e.g. “325 mg”
  • DOSAGE_REF : reference amount of form e.g. “1 tablet”, “2 mL suspension”
  • NATURE : must be one of two kinds :
    • SA (can be used as default value) : name of Active substance
    • FT : name of Tranformed substance
  • LK_NATURE :
    • Link from SA to FT

The LK_MOL_ATC table

This table is essential for the calculation of drug interactions.
The interactions' search engine uses the molecular composition of drugs. Each molecule must be linked to a code ATC (via its identifier from the database \e iam). Interactions are calculated on the basis of ATC codes.

Schema :

  • MOLECULE_CODE = link to COMPOSITION.MOLECULE_CODE
  • ATC_ID = link to INTERACTION_DATABASE.ATC.ID (not the CODE)

The PACKAGING table

This table is optional.

Schema :

  • UID : UID of the Drug
  • PACKAGE_UID : UID of the packaging
  • LABEL : label of the packaging
  • STATUS :
    • Active packaging → A
    • Inactive packaging → B
  • MARKETING :
    • Marketing cancelled → A
    • Unknown Marketing date → N
    • Commercialized → C
    • Suspended Marketing → S
  • DATE : most recent commercial update
  • OPTIONAL_CODE : can complete the MAIN_CODE

INFORMATIONS table [t1]

f1, f2, f3 are the SQLite database column names ; {CODE_ENUM} see plugins/drugsbaseplugins/constants_databaseschema.h; description of the column

Schema :

  • VERSION : Version of the db (please read the page : \ref versioning). See this as a primary key. FreeDiams will read the first selected row of the database as database informations.
  • NAME : Name of the database to display to the user. Translations are supported using syntax 'two-character ISO language code=name', unquoted, without any equal sign in the name and with line breaks delimiting multiple languages (see below).
    • fr=French name
      en=English name
      sp=Spanish
      de=Deutsch
      xx=All other languages
  • f1 {SOURCES_SID} 1(?)
  • f2 {SOURCES_DBUID} IDENTIFIANT : unique identifier (called “identifiant” in source) for FreeRx. TODO: use identifier instead of French word “identifiant” in source. This identifier must be unique for all drugs databases provided for FreeDiams
    • French AFFSSAPS database = “FR_AFSSAPS”
    • FDA database = “USA_FDA”
    • Canadian database = “CANADIAN”
    • French Theriaque database = “THERIAQUE”
  • f3 {SOURCES_MASTERLID} 1(?)
  • f4 {SOURCES_LANG} LANGUAGE_COUNTRY : language and country specificity. Format = language_country where :
    • language is a lowercase, two-letter, ISO 639 language code,
    • territory is an uppercase, two-letter, ISO 3166 country code,
    • and codeset and modifier are ignored.
  • f5 {SOURCES_WEB} WEBLINK : URI linking to the data source website
  • f6 {SOURCES_COPYRIGHT} LICENSE :
  • f7 {SOURCES_LICENSE} NULL
  • f8 {SOURCES_DATE} DATE : Date of release; format MUST BE formatted : yyyy-MM-dd
  • f9 {SOURCES_DRUGS_VERSION} 0.9.0
  • f10 {SOURCES_AUTHORS} AUTHOR : author of the database (eg Me)
  • f11 {SOURCES_VERSION} 0.9.0
  • f12 {SOURCES_PROVIDER} PROVIDER : Provider of the data (eg FDA, ANSM)
  • f13 {SOURCES_WEBLINK} URI link the the source database file
  • f14 {SOURCES_DRUGUID_NAME} DRUG_UID_NAME : Name to use for the drug UID (eg CIS for Fr)
  • f15 {SOURCES_ATC} ATC : boolean ; ATC is available for each drugs in the database
  • f16 {SOURCES_INTERACTIONS} INTERACTIONS : boolean : manages interactions
  • f17 {SOURCES_COMPL_WEBSITE} COMPLEMENTARY_WEBSITE : URI of web site with additional informations about database
  • f18 {SOURCES_PACKUID_NAME} PACK_MAIN_CODE_NAME : dosage form code Eg.: in Fr database, code CIP (Code Identifiant de Présentation)
  • f19 {SOURCES_COMPLETION} %
  • f20 {SOURCES_AUTHOR_COMMENTS} AUTHOR_COMMENTS : your comments
  • f21 DRUGS_NAME_CONSTRUCTOR : string to use for the drug name construction. Some tokens will be replaced by their values. You can use these tokens :
    • NULL value is replaced by “NAME” only
    • “NAME” : for the name of the drug ( == DRUGS.NAME)
    • “FORM” : for the name of the drug ( == DRUGS.FORM)
    • “ROUTE” : for the name of the drug ( == DRUGS.ROUTE)
    • “GLOBAL_STRENGTH” : for the strength of the drug ( == DRUGS.GLOBAL_STRENGTH)
    • Eg : NAME, FORM (GLOBAL_STRENGTH) == AMOXICILLINE, TABLETS (1g)
    • Eg : NAME, FORM, ROUTE - * GLOBAL_STRENGTH == AMOXICILLINE, TABLETS, ORAL - * 1g
  • f22 {SOURCES_FMFCOMPAT} COMPAT_VERSION : FreeRx application version dependency
  • f23 {SOURCES_OPENREACT_COMPAT} Obsolete (abandoned open react project)

The SEARCH_ENGINES table

This table allows developers to add search engines to help users. These search engines will be added to the Protocol Creator Dialog under the button Help and Informations.

Available tokens:

  • [[ONE_ATC_CODE]] –> 1 ATC CODE == 1 more link
  • [[DRUG_ATC]] –> if known the DRUGS.ATC value (ATC code)
  • [[ATC_CODES]] –> all know ATC Codes of the drug (and its molecular components)
  • [[ATC_LABELS]] –> all known ATC Label of the drug (and its molecular components)
  • [[DRUG_NAME]] –> exact value of the DRUGS.NAME field
  • [[CONSTRUCTED_DRUG_NAME]] –> the constructed drug name like inform in the INFORMATIONS.DRUGS_NAME_CONSTRUCTOR
  • [[DRUG_UID]] –> the UID of the drug

Interactions database

Interactions are calculated from ATC code of each component of drugs.
Some components are grouped into “interactions classes” which are shown in the ATC classification under the codes ZXX.
Each drugs databases that provides links from their components to ATC codes, give the possibilities to calculate the drugs interactions.

Table INTERACTIONS

Is mainly a link table.

  • ID : unique identifier of the interaction
  • ATC_ID1 : link to ATC.ID of the first interacting molecule or class
  • ATC_ID2 : link to ATC.ID of the second interacting molecule or class
  • INTERACTION_KNOWLEDGE_ID : link to the INTERACTION_KNOWLEDGE

[t24] Table INTERACTION_KNOWLEDGE

This table contains the content of the interaction.

  • f1 ID : unique identifier
  • f2 TYPE : type of interaction (encoded string)
    • A = drug duplication
    • U = INN duplication
    • Z = class duplication
    • P = Precaution
    • C = Contraindication
    • D = Inadvisable
    • T = Take in account
    • 450 = P450
    • I = Information
    • Y = P-glycoprotein
  • f3 RISK_FR : risk content in french
  • f4 MANAGEMENT_FR : management of the interaction in french
  • RISK_EN : risk content in english
  • MANAGEMENT_EN : management of the interaction in english
  • f5 XML : XML encoded (future use)

Database informations

Drugs databases are stored inside /Resources/databases/drugs.
You can use whatever file name you want. File name must end with .db file extension. The INFORMATIONS table will be parsed. If it does not contain errors, the drugs database will be considered as usable by FreeRx.

en/manuals/drugswidget/drugsdatabaseschema.txt · Last modified: 2017/03/18 03:40 by Jérôme Pinguet