Contextual Data

Last updated: 10/05/2022

Introduction

All the systems and databases within the MDR need to access so called 'contextual' data, which is of two main types:

• Context data: Data relating to the 'landscape' in which clinical research is carried out - organisations and people, countries and regions, systems for classifying keywords and topics etc.
• Controlled terminology: Data relating to the options available for categorised questions within the MDR itself.

Rather than replicate such data in every database, all of these data are stored in a separate context database, in the ctx and lup (look up) schemas respectively.
Postgres does not allow statements made in one database context to reference objects in another DB directly, so when processes in one of the source data databases require access to contextual data, one or both context schemas are imported into the database within a 'foreign table wrapper'. This allows the data, but not the table designs, to be read and manipulated. It does risk, however, cluttering up the databases with additional schema, so within the MDR such foreign table wrappers are always created on a temporary basis, when required, and are torn down again once the processing is completed (see the Managing Links between databases section in Data_Structures).
The sections below provide further details on the various types of contextual data in the system.

Organisation Data

The context database contains several tables concerned with organisational data, though only three are important in the current context:

• ctx.organisations
• ctx.org_other_names
• ctx.org_type_links

The organisations table itself lists organisations that are legal entities (e.g. universities, healthcare providers, publishers, pharmaceutical companies), that are part of such entities (e.g. trials units, laboratories, hospitals), or are government agencies (e.g. regulatory authorities, ethical review organisations), or are collaborations or research projects (e.g. H2020 projects). In other words an 'organisation' is loosely defined as any entity that is not an individual, but which is made up of people with a common purpose, that appears in the data. A group is defined as an organisation when it is convenient to do so, at a level that makes most sense in the context, not because it is legally or otherwise externally defined. In addition, some 'organisations' in the system, such as an international drugs company, are defined as a single organisation when legally they may be composed of many different organisations - e.g. subsidiaries in different countries.
The organisations table has the structure:

  id               integer    primary key
, default_name     varchar    
, lang_code        varchar    two letter language code
, display_suffix   varchar    usually location, when location is not obvious
, geog_id          integer    id from the geog_entities table 
, is_current       integer    1 by default, 0 if no longer exists
, notes            varchar    general description
, notes_source     varchar    e.g. web site
, source_date      date       date information last retrieved
, temp_type        varchar    temporary field - to be dropped
, type_id          integer    temporary field - to be dropped

while a few rows from the table are shown in figure 1.
In reality, few of the organisations listed have the full range of associated data, e.g. with notes and a notes source - though the first 6 fields are usually complete. The two temporary fields on the right of the table define the default type of the organisation, but these will be dropped in the future.

Figure 1: Sample rows from the ctx.organisations table

The structure of other org_other_names table is:

  id               integer    primary key
, org_id           integer    id of the organisation recvord
, default_name     varchar    default name - redundant but useful for isnpection
, qualifier_id     integer    the nature of the other name, e.g. abbreviation, translation
, other_name       varchar    the other name itself
, changes_language integer    whether or not it changes the language from the default name
, lang_code        varchar    the language code of the other name

Figure 2 shows some sample rows, in this case illustrating the different ways in which the pharmaceutical company Jannsen is included in the system.

Figure 2: Sample rows from the ctx.org_other_names table

The Updating contextual data section of Harvesting Data explains how the organisation tables are used to make the organisational data within various tables more standard.
Although little used at the moment the org_type_links table is designed to show the type(s) of the organisation, which may be multiple - for example an organisation may be both a funder and a charity, or both a research institution and a healthcare provider. The table is therefore essentially a join table between organisations and the ctx.org_types listing of possible organisation types. This table, and others, are likely to play a bigger role in the future once systems are developed to update organisational and other contextual data independently of the MDR, using a separate interface.

Topic Data

The topics or keywords associated with studies and journal papers is an obvious pool of data for searching and filtering, but that function is not helped by the fact that a 'topic' can often be expressed in many ways. Synonym files can be used (and within the MDR portal they are used) to support search functions, but it would also be good to try and rationalise the topic data in the database, converting terms to a standard vocabulary wherever possible, linking terms to a code and removing duplicates.
Because so much of the data already has MESH coding applied this system is the natural first choice to use for this process. It also has the advantages of being a well known, widely used and comprehensive system covering all aspects of clinical research. Most other systems are either relatively specialised (LOINC, MedDRA, ICD etc.) or are much less widely used - at least in the context of the source data systems (e.g. SNOMED CT).
The context database contains two MESH source tables, downloaded from the US National Library of Medicine, one for descriptive terms and the other for supplementary terns. These are combined into a table called mesh_lookup, with over 500,000 records but with just 4 fields:

• entry - a lower case text term that is one of the synonyms for a MESH code
• code - the MSH code itself
• term - the 'preferred' MESH term
• source - whether from the descriptive or supplementary list

Figure 3 provides some examples of the rowes in this table, referring to 3 MESH codes but 16 entry terms.

Figure 3: Sample rows from the ctx.mesh_lookup table

As is explained in the Updating contextual data section of Harvesting Data, this table is used in a lookup process to replace entry terms in the source data with the corresponding MESH term, although the original term is also preserved in the data. At the same time terms that have not been matched are also stored, to see if they can be mapped to a MESH code in the future.

Geographical Data

Geographical entities - countries, regions, continents, states, etc. - are referenced both within the main MDR, as a possible topic or keyword applied to some studies or papers, and within the contextual data , because organisations have a 'geographic scope'. They can be entered into the system under a variety of different names (US, U.S., USA. United States, United States of America, etc.) and it is therefore better to standardise and code them where possible.
The contextual data includes three tables - geog_entities, geog_entity_types, and geog_groupings - that list the countries and their codes, list the various types of entity available, and list the members of a composite entity (e.g. the states inside a country). The entity types are as follows:

• 0  System
• 1  Global
• 2  Continental
• 3  Regional (country grouping)
• 4  National
• 5  State (The largest of the administrative region types within a country, e.g. state, province, devolved nation, land)
• 6  County (The second tier administrative area in the country - e.g. county, départment)
• 7  Mixed

A section of the geog_entities is shown in Figure 4:

Figure 4: Sample rows from the ctx.geog_entities table

The iso codes (2 letter, 3 letter and numeric) associated with countries refer to the ISO-3166 country codes. Then geoname ids refer to the Ids to be found within the GeoNames geographical database (see http://www.geonames.org/). These include states as well as countries. Within the MDR, however, the id used for all geographical entities, e.g. within the organisations table, is the id field from this table, the internal accession number. This provides the greatest flexibility in labelling the geographic scope of entities.
The geog_groupings table is designed to support aggregation of geographically related data, by listing the constituent parts of entities. A small section of the table is shown in figure 5 below, in this case illustrating the member states of the EU:

Figure 5: Sample rows from the ctx.geog_groupings table

Publisher Data

The context schema includes three tables that relate print issns (pissns), electronic issns (eissns) and journal names to a publisher id, and a fourth table that gives the publisher name against that id. The data is largely derived from CrossRef, although the publisher's id is derived from its entry in the organisations table. The tables are required because PubMed citations identify the journal name, and its pissn and / or eissn, but not the publisher. The publisher is usually the managing organisation for a journal paper - in the sense that it is the publisher rather than the editorial board that decides whether a journal or paper is open access or not. The publisher's identity is therefore an important part of the metadata for a published paper. These four look up tables are therefore used to identify the publisher.

People Data

Ideally, there would be a 'contextual' list of people that would relate people's names, titles and affiliations to ORCID identifiers, and only the ORCID Ids would be used within the system. Unfortunately only a very small proportion of the names in the source data (as study leads or contacts, or as journal paper authors) come with ORCID identifiers. Names come in a variety of different formats - sometimes with titles, sometimes with first name rather than initials, sometimes with the family name before the given name - and there is no obvious pattern or consistency to this, even within a single source. In general, therefore, standardisation and rationalisation of name and affiliation data is not possible in the MDR, and such data remains largely as it is presented in the source data (though titles are dropped when they can be identified). There are no plans at the moment to create a contextual resource for people, unless and until adoption of ORCID identifiers becomes much more widespread.