Objective
Create a foundational system for storing medical images with associated patient information and study metadata. This enables organized medical imaging workflows while maintaining data integrity.
Step 1: Create Patients Table
Store patient demographic information.
CREATE TABLE medical_patients (
id INTEGER PRIMARY KEY,
patient_id VARCHAR(50) NOT NULL UNIQUE,
first_name VARCHAR(100),
last_name VARCHAR(100),
date_of_birth DATE,
gender VARCHAR(20),
medical_record_number VARCHAR(50) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create Imaging Studies Table
Store imaging study information.
CREATE TABLE imaging_studies (
id INTEGER PRIMARY KEY,
patient_id INTEGER NOT NULL,
study_uid VARCHAR(100) UNIQUE,
study_date DATE NOT NULL,
study_time TIME,
modality VARCHAR(20) NOT NULL,
study_description VARCHAR(255),
referring_physician VARCHAR(200),
performing_physician VARCHAR(200),
institution_name VARCHAR(200),
accession_number VARCHAR(50),
series_count INTEGER DEFAULT 0,
image_count INTEGER DEFAULT 0,
status VARCHAR(50) DEFAULT 'acquired',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (patient_id) REFERENCES medical_patients(id)
);
Step 3: Create Image Series Table
Organize images into series.
CREATE TABLE image_series (
id INTEGER PRIMARY KEY,
study_id INTEGER NOT NULL,
series_uid VARCHAR(100) UNIQUE,
series_number INTEGER,
series_description VARCHAR(255),
modality VARCHAR(20),
body_part VARCHAR(100),
patient_position VARCHAR(50),
series_date DATE,
series_time TIME,
image_count INTEGER DEFAULT 0,
FOREIGN KEY (study_id) REFERENCES imaging_studies(id)
);
Step 4: Create Medical Images Table
Store individual medical images.
CREATE TABLE medical_images (
id INTEGER PRIMARY KEY,
series_id INTEGER NOT NULL,
instance_uid VARCHAR(100) UNIQUE,
instance_number INTEGER,
image_data IMAGE(PNG),
image_thumbnail IMAGE(JPEG),
acquisition_date DATE,
acquisition_time TIME,
pixel_spacing VARCHAR(50),
slice_thickness DECIMAL(6, 2),
slice_location DECIMAL(8, 2),
window_center INTEGER,
window_width INTEGER,
rows INTEGER,
columns INTEGER,
bits_stored INTEGER,
file_size BIGINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (series_id) REFERENCES image_series(id)
);
Step 5: Create Study Notes Table
Store radiologist observations.
CREATE TABLE study_notes (
id INTEGER PRIMARY KEY,
study_id INTEGER NOT NULL,
note_type VARCHAR(50),
note_text TEXT,
created_by VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (study_id) REFERENCES imaging_studies(id)
);
Step 6: Insert Sample Patients
Add patient records.
INSERT INTO medical_patients (id, patient_id, first_name, last_name, date_of_birth, gender, medical_record_number) VALUES
(1, 'PAT-001', 'John', 'Smith', '1965-03-15', 'Male', 'MRN-10001'),
(2, 'PAT-002', 'Mary', 'Johnson', '1978-07-22', 'Female', 'MRN-10002'),
(3, 'PAT-003', 'Robert', 'Williams', '1955-11-08', 'Male', 'MRN-10003'),
(4, 'PAT-004', 'Sarah', 'Brown', '1982-04-30', 'Female', 'MRN-10004'),
(5, 'PAT-005', 'Michael', 'Davis', '1970-09-12', 'Male', 'MRN-10005');
Step 7: Insert Imaging Studies
Add study records.
INSERT INTO imaging_studies (id, patient_id, study_uid, study_date, study_time, modality, study_description, referring_physician, performing_physician, institution_name, accession_number, series_count, image_count, status) VALUES
(1, 1, '1.2.840.10001.1.1', '2024-01-15', '09:30:00', 'CT', 'CT Chest with Contrast', 'Dr. Anderson', 'Dr. Miller', 'City General Hospital', 'ACC-2024-001', 3, 450, 'completed'),
(2, 1, '1.2.840.10001.1.2', '2024-01-20', '14:15:00', 'XR', 'Chest X-Ray PA and Lateral', 'Dr. Anderson', 'Tech. Wilson', 'City General Hospital', 'ACC-2024-002', 1, 2, 'completed'),
(3, 2, '1.2.840.10001.2.1', '2024-01-18', '10:00:00', 'MR', 'MRI Brain without Contrast', 'Dr. Thompson', 'Dr. Garcia', 'City General Hospital', 'ACC-2024-003', 5, 180, 'completed'),
(4, 3, '1.2.840.10001.3.1', '2024-01-22', '11:45:00', 'CT', 'CT Abdomen Pelvis', 'Dr. White', 'Dr. Miller', 'City General Hospital', 'ACC-2024-004', 2, 380, 'in_progress'),
(5, 4, '1.2.840.10001.4.1', '2024-01-25', '08:30:00', 'US', 'Ultrasound Abdomen Complete', 'Dr. Lee', 'Tech. Brown', 'City General Hospital', 'ACC-2024-005', 1, 45, 'completed'),
(6, 5, '1.2.840.10001.5.1', '2024-01-26', '15:00:00', 'XR', 'X-Ray Lumbar Spine', 'Dr. Anderson', 'Tech. Wilson', 'City General Hospital', 'ACC-2024-006', 1, 4, 'pending_review');