Since ChEMBL was first released in 2009, the diversity of data sources and data types in the database has increased significantly. Increasingly, we are dealing with more complex assays such as measurement of drug pharmacokinetic parameters or toxicology data sets such as clinical biochemistry and tissue histopathology data. There are a number of problems handling these kinds of assays with the current data model/database schema. For example, since parameters such as compound doses or time points could not be recorded against individual activity measurements (only the whole assay) such experiments were typically split so that a separate assay was created for each compound or time point measured. This is obviously far from ideal. Another issue is that such experiments frequently measure or derive multiple endpoints from a particular assay (e.g., AUC, Cmax, tmax, t1/2 for a pharmacokinetic study) or produce large amounts of raw data that may need to be associated with summary-level information (e.g., toxicity measurements for individual animals).
For these reasons, we have introduced additional tables to the ChEMBL schema, as well as making some minor adjustments to existing ones (see diagram above for more details).
A full copy of the ChEMBL_24 schema is available here.
Changes To Existing Tables
ACTIVITIES:
PUBLISHED_TYPE, PUBLISHED_RELATION, PUBLISHED_VALUE, PUBLISHED_UNITS column have been renamed to TYPE, RELATION, VALUE, UNITS. This is to reflect the changing nature of the ChEMBL data (as we receive increasing numbers of deposited data sets in addition to extracting 'published' data) and also to keep consistency with other new tables. However, the
existing columns will also be retained for one release and then removed in ChEMBL_25. Therefore ChEMBL_24 will contain both PUBLISHED_TYPE and TYPE columns (for example), which will be populated with identical data. The STANDARD_TYPE/RELATION/VALUE/UNITS columns are not affected.
Several other new columns have been added to ACTIVITIES:
- TEXT_VALUE (used for non-numeric/qualitative measurements, which were previously stored in ACTIVITY_COMMENT)
- STANDARD_TEXT_VALUE (standardised version of TEXT_VALUE)
- UPPER_VALUE (used with VALUE column to capture measurements that are ranges)
- STANDARD_UPPER_VALUE (standardised version of UPPER_VALUE)
- TOID (Test Occasion Identifier, used to group together related activity measurements)
- SRC_ID (Indicates the source of the bioactivity data, link to SOURCE table)
ASSAY_PARAMETERS:
The format of the ASSAY_PARAMETERS table has been changed to make it consistent with the ACTIVITIES table and allow standardisation of assay parameter data (e.g., conversion of units). The previous columns of PARAMETER_TYPE and PARAMETER_VALUE have been replaced with:
- TYPE (equivalent to the previous PARAMETER_TYPE column)
- RELATION
- VALUE (which will store numeric parameter values)
- UNITS
- TEXT_VALUE (which will store non-numeric/qualitative parameter values)
There will also be an equivalent set of STANDARD columns, storing standardised parameter values (e.g., TIME in hr rather than minutes, concentrations in nM rather than M) and these should generally be used for querying.
- STANDARD_TYPE
- STANDARD_RELATION
- STANDARD_VALUE
- STANDARD_UNITS
- STANDARD_TEXT_VALUE
This change means that parameters that were previously split across multiple rows will now be grouped together. For example, previously 'DOSE' and 'DOSE_UNITS' would have been recorded as two separate parameters with no explicit link. Now, since the UNITS column has been added, these will be merged to just a single parameter called 'DOSE'.
PARAMETER_TYPE:
Table has been removed. Parameter types that have standardisation rules will be recorded in the ACTIVITY_STDS_LOOKUP table, as for activity types.
New Tables
ACTIVITY_PROPERTIES:
As mentioned previously, it was not possible in the old ChEMBL schema to record experimental parameters associated with an individual activity measurement rather than the whole assay. We have therefore created an ACTIVITY_PROPERTIES table to store this information. The format of this table is very similar to the ACTIVITIES and ASSAY_PARAMETERS tables and it can be joined to the ACTIVITIES table using the ACTIVITY_ID. There is an additional column called RESULT_FLAG that allows users to distinguish between true parameters/independent variables (RESULT_FLAG = 0) and measured properties/dependent variables e.g., 'HILL_SLOPE' or 'MAX_EFFECT' (RESULT_FLAG = 1), the latter also being included in this table where they are important in interpreting activity values.
ACTIVITY_SUPP:
The ACTIVITY_SUPP table is provided to store supplementary activity data that may not be appropriate to include in the main ACTIVITIES table. For example, in cases where IC50 data are included in the ACTIVITIES table, the supplementary table may be used to store the associated % inhibition values. Similarly, where summary-level toxicology results are stored in the ACTIVITIES table, results for individual animals may be included in ACTIVITY_SUPP. This does not mean that all raw data should be captured in ChEMBL in future, but in cases where this is deemed important to users, there is now a facility to do this.
The format of the ACTIVITY_SUPP table is again similar to ACTIVITIES, ASSAY_PARAMETERS and ACTIVITY_PROPERTIES. The table also contains an RGID (Record Grouping ID) column, which is used to group together multiple related supplementary measurements, and an SMID column which is used to link to the ACTIVITIES table (via the new
ACTIVITY_SMID and
ACTIVITY_SUPP_MAP tables).
Data Deposition
In order to facilitate data deposition, changes have been made to the ChEMBL loading process and the data deposition format. More details of the required deposition format will be made available soon (and we also hope to provide a submission portal in future), but in the meantime if you wish to submit data to us, please
contact us and we'll be happy to talk you through the process.
The improvements we've made allow us to update existing data sets (for example, where a depositor has additional activity measurements from a previously deposited assay and wants to add these to the database). They also allow a depositor to reference an existing set of compounds/assays and deposit data against them (for example, a different institution may deposit a set of assays/activities for compounds in the MMV Pathogen Box dataset). There are a few schema changes associated with these improvements (and there are also a few business rules/assumptions that will change). Firstly, key tables in ChEMBL now include a depositor defined ID:
- RIDX - Reference identifier stored in the DOCS table
- CIDX - Compound identifier stored in the COMPOUND_RECORDS table
- AIDX - Assay identifier stored in the ASSAYS table
These IDs are used by data depositors to refer to their references/compounds/assays and should be stable between depositions e.g., if a CIDX such as 'CPD0001' appears in two datasets from the same depositor, it must refer to the same compound. In practice, these fields are mainly used for loading/maintaining data but may be used in queries if a user or depositor wants to retrieve data based on an externally assigned identifier such as a compound research code.
- SRC_ID field has also been added to the DOCS and ACTIVITIES tables
This means that the DOCS table could become more redundant - if the same reference was provided by two different data sources, two separate documents would be created. In practice, this is not likely to happen very frequently.
An additional and important consequence of these changes is that is no longer necessarily the case that a given activity measurement, the assay it was measured in, and the compound that the activity was measured for will link to the same SRC_ID or DOC_ID. Queries should therefore take into account the SRC_ID and DOC_ID on all three tables (COMPOUND_RECORDS, ACTIVITIES and ASSAYS) when retrieving data. Please notes that in ChEMBL_24 there are no examples of this situation, but it is likely to occur in future releases.
Other Changes
A number of other minor schema changes have been made:
- COMPOUND_PROPERTIES.NUM_ALERTS removed (for full set of structural alerts see COMPOUND_STRUCTURAL_ALERTS table)
- MOLECULE_DICTIONARY.WITHDRAWN_CLASS added (high-level categories for WITHDRAWN_REASON e.g., Cardiotoxicity, Hepatotoxicity)
- PRODUCT_PATENTS.SUBMISSION_DATE added (from FDA Orange Book)
Data Changes
In addition to the schema changes outlined above, we have performed some software and data set updates that will affect some of the records in ChEMBL.
InChI version upgraded:
We have upgraded the version of Standard InChI used in ChEMBL to v1.05. This means the STANDARD_INCHI and STANDARD_INCHI_KEY in the COMPOUND_RECORDS table have changed for a very small number of compounds (16). Details of affected compounds will be provided with the release.
Compound properties now calculated with RDKit:
Fields in the COMPOUND_PROPERTIES table (with the exception of ACD_MOST_APKA, ACD_MOST_BPKA, ACD_LOGP and ACD_LOGD) are now calculated with RDKit, rather than BIOVIA Pipeline Pilot. Therefore some values may be slightly different from those in previous releases.
Reformatting of data sets:
Some previous data sets (Open TG-GATEs, DrugMatrix and Curated Drug Pharmacokinetic Data) have been re-formatted to take advantage of the new ACTIVITY_PROPERTIES and ACTIVITY_SUPP tables. This results in a smaller number of assays than before and as a result, assay and document CHEMBL_IDs for these data sets have changed. Please note that we may need to re-format some other legacy data in future releases, for example to re-group assays that have previously been split to accommodate different doses/time points, or to migrate results captured in the ACTIVITY_COMMENT field to the STANDARD_TEXT_VALUE field.
While we've described a lot of improvements here, most of these changes will not break existing code/queries (unless you're using the NUM_ALERTS property or querying the ASSAY_PARAMETERS table) for this release. However, it's worth taking note of the changes in the underlying data model (particularly if you are using the tox data sets or querying for particular sources) and checking that any assumptions still hold true.
If you need any more information about any of these changes please feel free to
contact us and we'll do our best to help!
