/* Requirements and Assumptions: A single doctor clinic Bookings can be made for patients We don't capture who made the booking We don't capture staff member details (receptionists) Doctors have many specialisations which can be used for filtering when booking Not in scope: insurance, different services with prices */ Table patient { id integer [primary key] first_name varchar last_name varchar gender_id integer [ref: > gender.id] date_of_birth date phone_number varchar email_address varchar address_line1 varchar address_line2 varchar state varchar country_id integer [ref: > country.id] created_at timestamp } Table country { id integer [primary key] country_name varchar } //This could be expanded to be a staff member TABLE doctor { id integer [primary key] first_name varchar last_name varchar gender_id integer [ref: > gender.id] created_at timestamp } TABLE gender { id integer [primary key] name varchar Note: 'Lookup table for genders' } TABLE specialisation { id integer [primary key] name varchar } TABLE doctor_specialisations { doctor_id integer [ref: > doctor.id] specialisation_id integer [ref: > specialisation.id] status_id integer [ref: > specialisation_status.id] review_date timestamp } Table specialisation_status { id integer [primary key] status_value varchar } TABLE appointment { id intger [primary key] patient_id integer [ref: > patient.id] doctor_id integer [ref: > doctor.id] appointment_datetime timestamp status_id integer [ref: > appointment_status.id] reason varchar created_at timestamp } TABLE appointment_status { id integer [primary key] name varchar Note: 'Captures statuses such as Scheduled or Cancelled' } TABLE appointment_note { id integer [primary key] appointment_id integer [ref: > appointment.id] notes text created_at timestamp created_by integer [ref: > doctor.id] Note: 'Captures the notes made for each appointment by a doctor.' } TABLE prescription { id integer [primary key] prescribed_appointment_id integer [ref: > appointment.id] medicine_name varchar [Note: 'This could be expanded to be a lookup table.'] dosage varchar frequency varchar instructions varchar created_at timestamp created_by integer [ref: > doctor.id] } TABLE patient_bill { id integer [primary key] appointment_id integer [ref: > appointment.id] amount decimal bill_status_id integer [ref: > bill_status.id] payment_method_id integer [ref: > payment_method.id] created_at timestamp bill_paid_datetime timestamp } TABLE payment_method { id integer [primary key] name varchar } TABLE bill_status { id integer [primary key] name varchar Note: 'A lookup table for bill status, such as Paid, Unpaid or Cancelled.' }