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, as and 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 the main two are

• ctx.organisations, and
• ctx.org_names

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' in the MDR 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 a multi-national 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    
, ror_id           varchar    the id within the ROR organisational database, if there is one
, display_suffix   varchar    usually location, when location is not obvious from the name
, scope_id         integer    an id from the ctx.geog_entities table that indictes any explicit geographical 'scope' of the organisation
, scope_notes      varchar    any additonal comments on the geographical scope
, is_current       integer    1 by default, 0 if no longer exists
, year_established integer    if known
, year_ceased      integer    if applicable and known
, created_on       timestamptz  created automatically

while a few rows from the table are shown in figure 1.
Note that the geographical scope is applied when there is an explicit geographical scope attached to an organisation's work, often for national or multi-national institutions. Most organisations, such as universities, hospitals, or commercial companies, while they are based in a country or countries, are not restrictred to a particular area in terms of the work they do, for example they may take students, patients and customers from outside their own geographical area.
The ROR (Research Organisation Registry) Id is available for many organisations that are also legal entities, and the URL that is the Id provides a link through to the set of associated ROR data - see ROR. As the ROR system expands it is hoped that a greater proportion of the organisations in the system will be linked to ROR Ids.

Figure 1: Sample rows from the ctx.organisations table

The structure of other org_names table is:

  id               integer    primary key
, org_id           integer    id of the organisation recvord
, qualifier_id     integer    the nature of the other name, e.g. abbreviation, translation
, lang_code        varchar    the language code of the other name
, script_code      varchar    the code of the script being used (for future use)
, name             varchar    the name itself

Figure 2 shows some sample rows, in this case illustrating the different names for the WHO and some of the trial registries in the system. The qualifier_id field is used to indicate ther 'type' of the name compared to the default (qualifier id = 1), for example an abbreviatioon, a translation, former name etc. The script_code field will be used to indicate the identity of any non-latin script, which, because of the use of translitteration schemes for chinese, japanese, russian etc., may vary independently of the language.

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 standardised.
The org_type_membership 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 lup.org_types listing of possible organisation types. Other tables include org_locations and org_links. These tables, and others, are likely to play a bigger role in the future as integration with the ROR system increases and systems are developed to update organisational and other contextual data independently of the MDR, using a separate interface.
The ROR data, integrated with the organisational data in the cts schema, is downloaded periodically from the ROR web site and is stored in tables in the ror schema.

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.