HealthNow schema

All tables live in the healthnow schema, so write healthnow.clinics in your queries.

healthnow.clinics

Clinics and departments in the network.

• clinic_id (INT) — PK
• name (VARCHAR(80))
• city (VARCHAR(40))
• clinic_type (VARCHAR(20)) — 'primary_care', 'specialty', 'hospital'

healthnow.doctors

Doctors and their specialty.

• doctor_id (INT) — PK
• full_name (VARCHAR(80))
• specialty (VARCHAR(30))
• clinic_id (INT) — FK → clinics.clinic_id

healthnow.patients

Registered patients (fictional).

• patient_id (INT) — PK
• full_name (VARCHAR(80))
• dob (DATE) — date of birth
• gender (VARCHAR(1)) — 'F', 'M', 'O'
• city (VARCHAR(40))
• registered_at (DATE)
• insurance_type (VARCHAR(12)) — 'private', 'government', 'self_pay'

healthnow.diagnoses

ICD-10 codes used in the network.

• diagnosis_code (VARCHAR(10)) — PK
• description (VARCHAR(120))
• category (VARCHAR(12)) — 'chronic', 'acute', 'preventive'

healthnow.visits

One row per encounter. Inpatient visits have a discharge date.

• visit_id (INT) — PK
• patient_id (INT) — FK → patients.patient_id
• doctor_id (INT) — FK → doctors.doctor_id
• clinic_id (INT) — FK → clinics.clinic_id
• visit_date (DATE) — admission date for inpatient stays
• visit_type (VARCHAR(12)) — 'outpatient', 'emergency', 'inpatient'
• diagnosis_code (VARCHAR(10)) — FK → diagnoses.diagnosis_code; primary diagnosis
• discharge_date (DATE) — nullable; inpatient only
• bill_amount (DECIMAL(10,2)) — INR

healthnow.lab_results

Lab tests ordered during visits, with the reference range used.

• lab_id (INT) — PK
• visit_id (INT) — FK → visits.visit_id
• test_type (VARCHAR(20)) — 'HbA1c', 'LDL', 'Fasting glucose', 'Hemoglobin', 'Creatinine'
• result_value (DECIMAL(6,2))
• result_unit (VARCHAR(10)) — '%', 'mg/dL', 'g/dL'
• reference_low (DECIMAL(6,2))
• reference_high (DECIMAL(6,2))
• result_flag (VARCHAR(6)) — 'NORMAL', 'HIGH', 'LOW' as recorded by the lab system
• result_date (DATE)

healthnow.medications

Prescriptions written during visits.

• med_id (INT) — PK
• visit_id (INT) — FK → visits.visit_id
• drug_name (VARCHAR(40))
• dosage_mg (INT)
• freq_per_day (INT)
• start_date (DATE)
• end_date (DATE) — nullable; planned end; NULL for open-ended prescriptions