How would you design a database schema to support a multi-center clinical trial tracking system?
Create a normalized schema for trials, centers, trial assignments, subjects, and visits, then write a query for trial 1 covering January through March 2025.
center_name, country, month_start, new_enrollments, total_visits, completed_visits, completion_rate_pct, prior_month_enrollments, and monthly_completed_visit_rank.center_name, then month_start ascending.| Column | Type | Description |
|---|---|---|
| trial_idPK | INT | Trial identifier |
| protocol_code | VARCHAR(20) | Clinical protocol code |
| trial_name | VARCHAR(100) | Trial name |
| phase | VARCHAR(40) | Clinical trial phase |
| Column | Type | Description |
|---|---|---|
| center_idPK | INT | Center identifier |
| center_name | VARCHAR(100) | Research center name |
| country | VARCHAR(60) | Center country |
| Column | Type | Description |
|---|---|---|
| trial_idPK | INT | Referenced trial |
| center_idPK | INT | Referenced center |
| activation_date | DATE | Date the center joined the trial |
| Column | Type | Description |
|---|---|---|
| subject_idPK | INT | Subject identifier |
| trial_id | INT | Referenced trial |
| center_id | INT | Recruiting center |
| subject_code | VARCHAR(30) | De-identified subject code |
| enrollment_date | DATE | Subject enrollment date |
| Column | Type | Description |
|---|---|---|
| visit_idPK | INT | Visit identifier |
| subject_id | INT | Referenced subject |
| visit_date | DATE | Scheduled or completed visit date |
| visit_status | VARCHAR(20) | Visit status |