Drugs database schema of FreeDiams

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

Drugs database

Name of the Drugs database file

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

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 for France, PZN Germany...). This designation can be completed in the table INFORMATIONS.

The various tables are:

So far, FreeDiams does not take into account the table PACKAGING.

The DRUGS table

Schema :

The COMPOSITION table

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

Schema:

The PACKAGING table

This table is optional.

Schema :

The INFORMATIONS table

Schema :

Interactions database

Till now, interactions' database is french only. May be a translator team can ensure english and deutsch versions.

Table IAM

Table IAM

This part need to be clarified...

Database informations

Drugs databases are stored inside /Resources/databases/drugs.
Name must formatted as above : drugs-XX.db where XX correspond to the two letters ISO encoded country : fr, us, de, sp...

SQL Schema : Drugs database

CREATE TABLE IF NOT EXISTS `DRUGS` (
  `UID` int(11) NOT NULL,
  `NAME` varchar(1000) NOT NULL,
  `FORM` varchar(500),
  `ROUTE` varchar(100),
  `ATC` varchar(7),
  `GLOBAL_STRENGTH` varchar(40),
  `TYPE_MP` varchar(1),
  `AUTHORIZATION` varchar(1),
  `MARKETED` bool NOT NULL DEFAULT 1,
  `LINK_SPC` varchar(250)
);

CREATE TABLE IF NOT EXISTS `COMPOSITION` (
  `UID` int(10) NOT NULL,
  `MOLECULE_FORM` varchar(100),
  `MOLECULE_CODE` int(11) NOT NULL,
  `MOLECULE_NAME` varchar(200) NOT NULL,
  `DOSAGE` varchar(100)  NOT NULL,
  `DOSAGE_REF` varchar(50)  NOT NULL,
  `NATURE` varchar(2) NOT NULL DEFAULT "SA",
  `LK_NATURE` int(11) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS `PACKAGING` (
  `UID` int(11) NOT NULL,
  `PACKAGE_UID` int(20) NOT NULL,
  `LABEL` varchar(500) NOT NULL,
  `STATUS` varchar(1),
  `MARKETING` int(1) NOT NULL DEFAULT 1,
  `DATE` varchar(25),
  `OPTIONAL_CODE` int(20)
);

CREATE TABLE IF NOT EXISTS `INFORMATIONS` (
  `VERSION` varchar(10),
  `NAME` varchar(2000),
  `IDENTIFIANT` varchar(50),
  `COMPAT_VERSION` varchar(10),
  `PROVIDER` varchar(200),
  `WEBLINK` varchar(500),
  `AUTHOR` varchar(200),
  `LICENSE` varchar(1000),
  `LICENSE_TERMS` varchar(10000),
  `DATE` varchar(20),
  `DRUG_UID_NAME` varchar(50),
  `PACK_MAIN_CODE_NAME` varchar(50),
  `ATC` bool NOT NULL,
  `INTERACTIONS`  bool NOT NULL DEFAULT FALSE,
  `AUTHOR_COMMENTS` varchar(10000),
  `LANGUAGE_COUNTRY` varchar(5),
  `DRUGS_NAME_CONSTRUCTOR` varchar(200)
);

SQL Schema : Interactions database

CREATE TABLE IF NOT EXISTS `IAM_DENOMINATION` (
`ID_DENOMINATION` int(10) NOT NULL ,
`DENOMINATION` varchar( 200 ) NOT NULL
);

CREATE TABLE IF NOT EXISTS `IAM_IMPORT` (
`IAM_ID` INTEGER PRIMARY KEY,
`ID1` int(10) NOT NULL ,
`ID2` int(10) NOT NULL ,
`TYPE` INT UNSIGNED NOT NULL ,
`TEXT_IAM` VARCHAR( 2000 ) NULL ,
`TEXT_CAT` VARCHAR( 2000 ) NULL
 );

CREATE TABLE IF NOT EXISTS `ATC` (
`ID` INTEGER PRIMARY KEY,
`CODE` VARCHAR(7) NULL ,
`ENGLISH` VARCHAR(127) NULL ,
`FRENCH` VARCHAR(127) NULL
 );

User manual - FreeDiams