SQL Clinic Database

A MySQL database project for a small walk-in clinic. The project models patients, doctors, staff, appointments, diagnostic tests, visit notes, payments, and staff shifts.

This project was built as a cleaned and expanded portfolio version of an academic database design. The goal was to practice relational database design, normalization, foreign keys, seed data, and reporting queries.

Tech Stack

• MySQL
• SQL DDL
• SQL queries
• MySQL Workbench

Project Structure

sql-clinic-database/
├── sql/
│   ├── 01_schema.sql
│   ├── 02_seed_data.sql
│   └── 03_queries.sql
├── docs/
│   ├── data-model.md
│   └── query-catalog.md
├── README.md
└── .gitignore

Database Overview

The database is centered around appointments. Each appointment connects a patient, a doctor, a service type, an appointment status, and optionally the staff member who booked it.

Main areas covered by the schema:

• Patient and doctor records
• Staff records and shift assignments
• Appointment scheduling
• Appointment statuses and service types
• Doctor-patient enrollments
• Visit notes
• Diagnostic tests and test results
• Appointment payments

Main Tables

Table	Purpose
patients	Stores patient contact and health card information
doctors	Stores doctor profiles, specialties, and license numbers
staff	Stores clinic staff information
appointments	Stores patient appointments with doctors
appointment_statuses	Stores appointment status values
service_types	Stores clinic service types and base fees
enrollments	Tracks patient enrollment with doctors
visit_notes	Stores medical notes for appointments
diagnostic_tests	Stores available diagnostic test types
appointment_tests	Links appointments to ordered diagnostic tests
test_results	Stores results for ordered tests
payments	Stores payment records for appointments
shifts	Stores clinic shift times
staff_shifts	Links staff members to assigned shifts

SQL Concepts Demonstrated

• CREATE TABLE
• Primary keys
• Foreign keys
• AUTO_INCREMENT
• UNIQUE constraints
• One-to-many relationships
• Many-to-many relationship using a junction table
• JOIN and LEFT JOIN
• GROUP BY and HAVING
• UNION
• CASE
• Date and time functions
• Basic reporting queries

How to Run

1. Open MySQL Workbench or another MySQL client.
2. Run the schema file:

source sql/01_schema.sql;

3. Run the seed data file:

sql/02_seed_data.sql

4. Run the reporting queries:

sql/03_queries.sql

The schema file uses DROP DATABASE IF EXISTS clinic_db;, so running it will recreate the database from scratch.

Example Queries

The project includes reporting queries for:

• Appointment schedules with patient and doctor names
• Completed appointments with visit notes
• Upcoming scheduled appointments
• Patients without active doctor enrollment
• Active patient-doctor enrollments
• Appointment counts by doctor
• Patients with multiple appointments
• Diagnostic tests ordered for appointments
• Abnormal test results
• Paid revenue by service type
• Pending or unpaid payments
• Combined patient and staff contact list
• Staff shift schedules with calculated shift duration

See docs/query-catalog.md for a full query summary.

Data Model

See docs/data-model.md for the database table overview and ER diagram.

Notes

This is a database-focused portfolio project. It does not include a user interface or backend API. The purpose is to demonstrate SQL schema design, relational modeling, sample data creation, and practical reporting queries.