Difference between revisions of "Contextual Data"

From ECRIN-MDR Wiki
Jump to navigation Jump to search
(Introduction)
(Publisher Data)
 
(69 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
<p style="color:blue; text-align:right"><small>'''''Last updated: 10/05/2022'''''</small></p>
 
===Introduction===
 
===Introduction===
 
All the systems and databases within the MDR need to access so called 'contextual' data, which is of two main types:
 
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 topic etc.
+
* 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.
 
* Controlled terminology: Data relating to the options available for categorised questions within the MDR itself.
Both these data types are stored in the context database, in the ctx and lup schemas respectively.
+
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.<br/>
 +
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]]''').<br/>
 +
The sections below provide further details on the various types of contextual data in the system.
  
===Contextual Data===
+
====Organisation Data====
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.<br/>
 +
The organisations table has the structure:
 +
<div style="font-family: monospace; font-size: 13px">
 +
<pre>
 +
  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
 +
</pre>
 +
</div>
 +
while a few rows from the table are shown in figure 1. <br/>
 +
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.<br/>
 +
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 [https://ror.org/ 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.
 +
[[File:orgs 2.png|center||]]
 +
<div style="text-align:center">'''Figure 1:''' Sample rows from the ctx.organisations table</div>
 +
The structure of other org_names table is:
 +
<div style="font-family: monospace; font-size: 13px">
 +
<pre>
 +
  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
 +
</pre>
 +
</div>
 +
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 the 'type' of the name compared to the default (qualifier id = 1), for example an abbreviation, 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.
 +
[[File:orgnames 2.png|center||]]
 +
<div style="text-align:center">'''Figure 2:''' Sample rows from the ctx.org_other_names table</div>
 +
Different names are required in the system for any particular organisation because the names are almost always input into source systems as free text, allowing for a huge variety of spellings and forms. The updating contextual data section of '''[[Harvesting Data]]''' explains how the organisation tables are used to make the organisational data within various tables = so far as is possible - more standardised.<br/>
 +
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.<br/>
 +
The ROR data, integrated with the organisational data in the ctx schema, is downloaded periodically from the ROR web site and is stored in tables in the '''ror''' schema.
  
Geographical Data  
+
====Topic Data====
 +
The topics or keywords associated with studies and journal papers is an obvious pool of data for searching and filtering, but that functionality 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.<br/>
 +
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).<br/>
 +
The MESH data is downloaded periodically as part of a larger periodic download of UMLS data, stored in the and '''umls''' schema, and includes two main MESH source tables, 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 MESH 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.
 +
[[File:mesh codes.png|center||]]
 +
<div style="text-align:center">'''Figure 3:''' Sample rows from the ctx.mesh_lookup table</div>
 +
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.
  
Topic Data
+
====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 may have a geographic scope_id. They can be entered into the source systems 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.<br/>
 +
The contextual data includes two tables - ctx.geog_entities and lup.geog_entity_types - that list countries and other geographical entities. The entity types are as follows:
 +
* 1&nbsp;&nbsp;Global                  : A single entity with a global extent
 +
* 2&nbsp;&nbsp;Continental            : Geonames listed continents
 +
* 3&nbsp;&nbsp;Regional                : Geonames country groupings
 +
* 4&nbsp;&nbsp;National                : Geonames countries
 +
* 5&nbsp;&nbsp;State                  : Geonames admin1s, the top-level administrative regions
 +
* 6&nbsp;&nbsp;Political or economic  : Groupings with a political or economic purpose, rather than geographical proximity
 +
* 7&nbsp;&nbsp;Other                  : Any entity not included in other categories
 +
A section of the geog_entities is shown in Figure 4, and includes some regional groupings as well as some countries. In fact the most common type of entity is 'State', the main adminitrative division listed within each country. The source of the geographic data is the geonames website ([https://www.geonames.org/ geonames]) and the data is periodically downloaded from there and stored in the '''geonames''' schema, before some basic processing to create the geog_entities table.<br/>
 +
[[File:countries 2.png|center||]]
 +
<div style="text-align:center">'''Figure 4:''' Sample rows from the ctx.geog_entities table</div>
 +
<br/>
  
Publisher Data
+
====Publisher Data====
 +
The context schema includes 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. The 'source' tables for this data, as periodically downloaded from crossref, are stored in the '''crossref''' and '''pubs''' schemas.
  
===Controlled Terminology===
+
====People Data====
Many of the data fields in the MDR system are categorised, i.e. they are constrained to hold one of a predefined set of possible values. <br>
+
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.
For example, a clinical study may fall into one of several different main ‘types’, according to the broad methodology used:
 
* Interventional
 
* Observational
 
* Observational [Patient Registry]
 
* Expanded access
 
* Funded programme
 
Although clinical studies are usually categorised in similar ways, different systems can use different terms for those categories. Using the MDR’s controlled terminology, studies labelled as ‘clinical trial’, ‘randomised trial’, or ‘active intervention’ would all be mapped to a study type of ‘Interventional’, while other studies, described variously as ‘off-label’, ‘compassionate use’, or ‘pre-licence’, would all be mapped to ‘Expanded Access’.<br>
 
<br>
 
The MDR system uses a set of ‘look up’ tables, each one providing the possible values of a categorised data field within the system. In the context of a user interface, each lookup table would often be used as the source of a dropdown or list box that displayed the categories for search or filtering purposes.  
 
Each look up table lists the options available for that data field, and almost all have a core common structure:
 
* An integer ''id'' field, that provides a key for each option. The database only has to store the id to represent the category being used, making storage much more efficient.
 
* A string ''name'' field, with the short name of the category, as it would appear – for instance – in a dropdown box that displayed the alternatives available.
 
* A text ''description'' field, that provides, when necessary, a brief explanation of what the category meant.
 
* An integer ''list_order'' field, which allows the default order in a display or report (normally that of the id, as the primary key) to be over-ridden, by ordering the records according to the value of this field.
 
A few look up tables have one or two additional fields but the great majority follow the pattern above.
 
<br>
 
The categories used are normally as already exist within DataCite or other key systems, augmented if necessary by ECRIN to better cover the full range of categories required for clinical research data objects. For instance the listed study types are taken from the study type classification found within the ClinicalTrials.gov trials registry.
 
The lookup tables contain two additional ‘audit’ field:
 
* A string ''source'' field, denoting the system from which the term was originally taken. Terms created by the MDR team are labelled as ‘ECRIN’
 
* A date ''date_added'' field, that indicates the date a category was agreed as being required by the MDR system (though full implementation may be later). If a term has been proposed but not yet agreed this date is null.
 
The study_types table, below, illustrates how this structure works in the case of study categories.
 
<br>
 
{| class="wikitable"
 
|-
 
! id !! name !! description !! list_order !! source !! date_added
 
|-
 
| 0|| Not yet known|| Dummy value supplied by default on entity creation.|| 99 || ECRIN || 2019-02-08
 
|-
 
| 11|| Interventional|| A clinical trial.|| 10|| ClinicalTrials.gov || 2019-02-08
 
|-
 
| 12 || Observational|| Any form of non-interventional research.|| 20|| ClinicalTrials.gov || 2019-02-08
 
|-
 
| 13|| Observational Patient Registry|| Collecting data for a designated registry.|| 30|| ClinicalTrials.gov || 2019-02-08
 
|-
 
| 14|| Expanded access|| Off label usage of a new product for individuals.|| 40|| ClinicalTrials.gov || 2019-02-08
 
|-
 
| 15|| Funded programme|| With a single or linked series of grants.|| 50|| ClinicalTrials.gov || 2019-02-08
 
|}
 
 
 
At present the MDR system contains 29 look up tables, with most indicating that they refer to categorised fields by their name, usually <something>_types, occasionally _codes, categories or _classes.
 
 
 
* [[study attributes]]  (study_types, study_statuses, gender_eligibility_types)
 
* [[study features]]  (feature_types and categories of phase, allocation method, masking, time perspective etc.)
 
* [[study_relationship_types]]
 
* [[object_classes]]
 
* [[object_types]]
 
*      [[object_filter_types]]
 
* [[object_access_types]]
 
* [[object_instance_types]]
 
* [[object_relationship_types]]
 
*      [[contribution_types]]
 
*      [[dataset_consent_types]]
 
*      [[dataset_de-identification_levels]]
 
*      [[dataset_record_key_types]]
 
* [[date_types]]
 
* [[description_types]]
 
* [[doi_status_types]]
 
* [[identifier_types]]
 
* [[language_codes]]
 
*      [[language_usage_types]]
 
* [[resource_types]]
 
* [[title_types]]
 
* [[topic_types]]
 
* [[topic_vocabularies]]
 
* [[units]]  (for size and time)
 
* [[composite_hash_types]]
 
 
 
Each of these tables is described in more detail on its own wiki page.
 
<br><br>
 
Another potential use of controlled vocabularies relates to organisations and people. References to both of these entities are often confused because any organisation or person may be known by a variety of names. The very large numbers of organisations and people in the MDR system, however, mean that it is impractical to provide codes for them all, or try to match all the names in source systems to those codes. Some degree of matching of organisations may be possible in the future. The problems of people and organisation representation are described in another section.
 
<br><br>
 

Latest revision as of 17:30, 11 May 2022

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.

Orgs 2.png
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 the 'type' of the name compared to the default (qualifier id = 1), for example an abbreviation, 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.

Orgnames 2.png
Figure 2: Sample rows from the ctx.org_other_names table

Different names are required in the system for any particular organisation because the names are almost always input into source systems as free text, allowing for a huge variety of spellings and forms. The updating contextual data section of Harvesting Data explains how the organisation tables are used to make the organisational data within various tables = so far as is possible - 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 ctx 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 functionality 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 MESH data is downloaded periodically as part of a larger periodic download of UMLS data, stored in the and umls schema, and includes two main MESH source tables, 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 MESH 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.

Mesh codes.png
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 may have a geographic scope_id. They can be entered into the source systems 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 two tables - ctx.geog_entities and lup.geog_entity_types - that list countries and other geographical entities. The entity types are as follows:

  • 1  Global : A single entity with a global extent
  • 2  Continental : Geonames listed continents
  • 3  Regional : Geonames country groupings
  • 4  National : Geonames countries
  • 5  State : Geonames admin1s, the top-level administrative regions
  • 6  Political or economic : Groupings with a political or economic purpose, rather than geographical proximity
  • 7  Other : Any entity not included in other categories

A section of the geog_entities is shown in Figure 4, and includes some regional groupings as well as some countries. In fact the most common type of entity is 'State', the main adminitrative division listed within each country. The source of the geographic data is the geonames website (geonames) and the data is periodically downloaded from there and stored in the geonames schema, before some basic processing to create the geog_entities table.

Countries 2.png
Figure 4: Sample rows from the ctx.geog_entities table


Publisher Data

The context schema includes 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. The 'source' tables for this data, as periodically downloaded from crossref, are stored in the crossref and pubs schemas.

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.