DBW Course · Data Model
Hospital Clinical Trials
Data Model
Relational database schema for a hospital clinical trials management system. Covers pseudonymised participant records, longitudinal measurements and file attachments, variable definitions, and role-based access control scoped per study. Implemented in MySQL.
Entity-Relationship Diagram
Table Definitions
studies
study_idINTPK
study_codeVARCHAR(50)UNIQUE
titleVARCHAR(255)
descriptionTEXT
start_dateDATE
end_dateDATE
statusENUMdraft|recruiting…
created_atDATETIME
participants
participant_idINTPK
pseudonymVARCHAR(50)UNIQUE · no PII
year_of_birthYEAR
sexENUMM|F|other|unknown
created_atDATETIME
enrollments
enrollment_idINTPK
study_idINTFK
participant_idINTFK
enrollment_dateDATE
statusENUMactive|withdrawn…
variables
variable_idINTPK
study_idINTFK
nameVARCHAR(100)
unitVARCHAR(50)e.g. mmHg
data_typeENUMnumeric|text…
is_requiredTINYINT(1)
timepoints
timepoint_idINTPK
study_idINTFK
nameVARCHAR(100)e.g. Baseline
day_offsetINTdays from enrolment
measurements
measurement_idINTPK
enrollment_idINTFK
variable_idINTFK
timepoint_idINTFK
value_numericDOUBLE
value_textTEXT
collected_byINTFK
data_files
file_idINTPK
enrollment_idINTFK
timepoint_idINTFK · nullable
filenameVARCHAR(255)
file_typeVARCHAR(50)MIME type
file_pathVARCHAR(500)
uploaded_byINTFK
users
user_idINTPK
usernameVARCHAR(50)UNIQUE
password_hashVARCHAR(255)
emailVARCHAR(150)UNIQUE
full_nameVARCHAR(150)
is_activeTINYINT(1)
roles
role_idINTPK
nameVARCHAR(50)UNIQUE
descriptionTEXT
user_roles
user_role_idINTPK
user_idINTFK
role_idINTFK
study_idINTFK · nullable
granted_atDATETIME
Relationships
| From | To | Cardinality | Description |
|---|---|---|---|
| studies | enrollments | 1 : N | A study has many enrolled participants |
| participants | enrollments | 1 : N | A participant can be enrolled in multiple studies |
| studies | variables | 1 : N | Each study defines its own set of measurement variables |
| studies | timepoints | 1 : N | Each study has scheduled assessment timepoints |
| enrollments | measurements | 1 : N | Longitudinal data items collected per enrolled participant |
| enrollments | data_files | 1 : N | File attachments associated with an enrolment |
| variables | measurements | 1 : N | Each measurement records a value for a defined variable |
| timepoints | measurements | 1 : N | Measurements are taken at a specific visit/timepoint |
| timepoints | data_files | 1 : N (optional) | Files may be associated with a timepoint |
| users | measurements | 1 : N | Records who collected the measurement |
| users | data_files | 1 : N | Records who uploaded the file |
| users | user_roles | 1 : N | Role assignments per user |
| roles | user_roles | 1 : N | A role can be assigned to many users |
| studies | user_roles | 1 : N (optional) | study_id NULL = global role; set = study-scoped role |
MySQL Schema