Explore RAMSES data model

[razekmh]

TLDR: understand the data model and table structure of RAMSES to help in mapping OMOP data later

Background:
Ramses has its own data model which is mapped into a list of tables in a database (DuckDB). We need to understand this data model enough so that we can map OMOP data to it. Additionally, if there is primary and derived data, we need to understand which is which and how derive the data.

Tasks

• read and follow the Objects and classes in Ramses.
• Check the example database structure.
• Check data transformation methods in RAMSES code.
• Summaries the general findings to document our of the data model

[zsenousy]

RAMSES Primary Data Tables

patients

This table contains fundamental demographic and identification data for each patient (e.g., NHS number, forename, surname, date of birth, etc.). Since this data is typically collected directly from patient records, it is classified as primary.

drug_prescriptions

This table captures raw data on the medications prescribed to patients, including details like prescription ID, drug name, dose, and dates. Since this information is directly entered by healthcare professionals during patient care, it's primary data.

drug_administrations

This table records when and how prescribed drugs were administered to patients, including details like administration ID, drug name, dose, and route. This data is collected at the time of administration, making it primary data.

inpatient_episodes

This table contains data on inpatient care episodes, including patient ID, encounter ID, admission, and discharge dates. These records are collected during patient care, so they are considered primary data.

microbiology_specimens

This table records specimens collected for microbiology tests, including specimen type, status, and collection date. This data is gathered at the time of specimen collection and is, therefore, primary.

microbiology_isolates

This table captures data on microorganisms isolated from specimens, including organism name and code. The data is directly derived from lab results, making it primary.

microbiology_susceptibilities

This table contains susceptibility test results for isolated microorganisms, indicating which antibiotics are effective. The results are directly obtained from lab tests, making this primary data.

inpatient_investigations

This table holds data on various investigations performed on inpatients, including observations, results, and timestamps. These records are collected during clinical investigations, classifying them as primary data.

inpatient_diagnoses

This table contains diagnosis data recorded during inpatient care, including ICD codes and diagnosis positions. Since this data is directly entered by clinicians during patient encounters, it is primary data.

inpatient_ward_movements

This table captures the movements of patients between wards during their inpatient stay, including ward codes and movement dates. These are real-time records made during patient care, making them primary data.

reference_icd

This table contains reference data for ICD codes, which are standardized medical codes for diagnoses. While this data is reference material, it's not derived from other tables within this schema, so it's primary.

reference_icd_ccs

Similar to the reference_icd table, this table contains reference data for the Clinical Classifications Software (CCS) categories derived from ICD codes. It's primary because it provides reference information used directly in patient records.

reference_icd_ccsr

his table contains reference data for the Clinical Classifications Software Refined (CCSR) categories, which are also derived from ICD codes. Like the other reference tables, it's classified as primary data.

reference_icd_infections

This table provides reference information for infection-related ICD codes, which are used directly in clinical records. It's considered primary because it contains standardized reference data.

reference_icd_comorbidity

This table contains comorbidity-related ICD codes and associated Charlson weights. Since it provides essential reference information for patient records, it is primary data.

reference_aware

This table provides reference data related to AMR and the ATC classification system. It is primary because it offers reference information for drug prescriptions and related activities.

[zsenousy]

RAMSES Derived Data Tables

bridge_encounter_therapy_overlap

This table represents derived data that calculates overlaps between therapy episodes and encounters. The data here is derived by combining information from drug_therapy_episodes and inpatient_episodes.

bridge_episode_prescription_initiation

This table appears to be derived data that links inpatient episodes with prescription initiation dates. It's created by analysing data from inpatient_episodes and drug_prescriptions.

bridge_episode_prescription_overlap

This table probably contains derived data showing overlaps between inpatient episodes and drug prescriptions. The data is a result of combining multiple primary sources.

drug_therapy_episodes

This table represents episodes of drug therapy that are aggregated or derived from individual prescriptions or administrations. It combines data from primary tables like drug_prescriptions and drug_administrations to summarise therapy periods.

drug_prescriptions_edges

This table represents relationships between drug prescriptions, such as switches or combinations. The data here is derived from analysing multiple drug_prescriptions records.