Database Design

1. Database Overview

The Medical Booking System will use PostgreSQL as the main relational database.

The database will store information about users, doctors, medical specialties, appointments and available time slots.

The database structure will be designed to maintain relationships between users, doctors and appointments.

2. Users

The users table will store information about registered users.

Main fields:

• id — unique user identifier
• name — user's full name
• email — user's email address
• password_hash — hashed user password
• created_at — account creation date

A user can create multiple appointments.

3. Doctors

The doctors table will store information about medical professionals.

Main fields:

• id — unique doctor identifier
• name — doctor's full name
• specialty_id — reference to the doctor's specialty
• experience — years of professional experience
• bio — short professional description

A doctor can have multiple available time slots and multiple appointments.

4. Specialties

The specialties table will contain medical specialties.

Main fields:

• id — unique specialty identifier
• name — specialty name

Examples:

• Cardiology
• Dermatology
• Neurology
• Pediatrics
• Dentistry

One specialty can belong to multiple doctors.

5. Available Slots

The available_slots table will store appointment times available for each doctor.

Main fields:

• id — unique slot identifier
• doctor_id — reference to the doctor
• date — available appointment date
• start_time — appointment start time
• is_available — indicates whether the slot is available

A doctor can have multiple available time slots.

6. Appointments

The appointments table will store booked appointments.

Main fields:

• id — unique appointment identifier
• user_id — reference to the user
• doctor_id — reference to the doctor
• slot_id — reference to the selected time slot
• reason — reason for the appointment
• status — appointment status
• created_at — appointment creation date

Possible appointment statuses:

• confirmed
• cancelled
• completed

7. Relationships

The main database relationships are:

User → Appointments

One user can have many appointments.

users.id → appointments.user_id

Doctor → Appointments

One doctor can have many appointments.

doctors.id → appointments.doctor_id

Specialty → Doctors

One specialty can have many doctors.

specialties.id → doctors.specialty_id

Doctor → Available Slots

One doctor can have many available time slots.

doctors.id → available_slots.doctor_id

Available Slot → Appointment

One available slot can be associated with an appointment.

available_slots.id → appointments.slot_id

8. Database Structure

The planned database structure is:

Users ↓ Appointments ↑ Doctors ↓ Specialties

Doctors ↓ Available Slots

This structure allows the system to connect users with doctors and available appointment times.

9. Data Integrity

The database should maintain data integrity by using:

• Primary keys for unique records
• Foreign keys for relationships
• Required fields for important information
• Unique constraint for user email addresses
• Validation for appointment dates and times
• Appointment status values with predefined options

The system should also prevent two users from booking the same available time slot.

10. Future Expansion

The database structure can be expanded if additional functionality is added.

Possible future tables include:

• reviews — patient reviews of doctors
• notifications — appointment notifications
• medical_records — medical information if required by the project
• doctor_schedules — recurring doctor schedules

These features are outside the initial project scope and may be added later if necessary.