Clinical Trials Database

Model Architecture Overview

This database model was designed to manage a hospital's clinical trials support service. It handles requirements such as multi-trial support, dynamic clinical variables, and longitudinal patient data.

Key Design Features:

• Pseudonymization: patient identities are strictly separated from study data using surrogate participant IDs to comply with legal restrictions.
• Longitudinal tracking: a specific visit entity links participants to their clinical data across time, capturing series of hospital visits.
• Conctat: a specific entity has been created to have a set of basic contact items, able for different types of entries.

Database Mechanics & Relationships:

• Primary Keys (PK): Every table utilizes a dedicated primary key to uniquely identify records and maintain data integrity:
  • idclinical_study, idparticipant, idlab, idcontact, idusers, idvisit, idclinical_data


• One-to-One (1:1) Relationships:
  • contact <-> users
  • contact <-> lab
  • contact <-> clinical_study


• One-to-Many (1:N) Relationships:
  • lab -> clinical_study: A lab can manage multiple studies, but a study has only one lab.
  • clinical_study -> participant: A study contains multiple participants.
  • participant -> clinical_data: One participant generates many clinical data items over time.
  • visit -> clinical_data: One scheduled visit groups together multiple collected clinical data points.
  • users -> clinical_data: One user/staff member can enter multiple data records.