Multi Clinic Appointment System Database Design Tutorial
Introduction
If you’re a busy individual, who has difficulty juggling more than one appointment at a time, the Multi-Clinic Appointment System may be the answer. This system is designed for patients with an array of medical appointments taken on a single day by a single clinic.
First and foremost, the Multi-Clinic Appointment System is reliable in that it provides accurate scheduling for doctors and nurses alike.
The idea behind the Multi Clinic Appointment System (MCAS) is straightforward: you can make an appointment online for any service at any participating clinic. It simplifies the process and saves time while still maintaining a standard of care comparable to traditional arrangements.
Database Design Tutorial
This article and video tutorial will show you how to design a multi-clinic appointment system and the steps you need to take to add clinic hours and staff members. You can then add appointments for one or more clinics by entering data for patients and doctors at any time slot.
The tutorial will go over the steps to come up with a multi-clinic appointment system database design. This includes planning out your database, selecting an appropriate DBMS, developing the tables, generating sample data for testing purposes, and finally implementing it.
tbl_patient – this table will store the information of the patients who can access the records of the system.
- patient_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- first_name – first name of the patient
- middle_name- middle name of the patient
- last_name – last name of the patient
- birthdate – date of birth of the patient
- complete_address – the complete address of the patient
- contact_number – contact number of the patient, preferably mobile or cellphone number.
- proof_of_identity – photo of the patient as proof of identity
- username – the desired username of the patient, combined with the password to log in to the system
- password – the desired password of the patient, combined with the username to log in to the system.
- status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
Create SQL Statement – the statement below is used to create the tbl_patient, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_patient` ( `patient_id` int(11) NOT NULL, `first_name` varchar(30) NOT NULL, `middle_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `birthdate` date NOT NULL, `complete_address` text NOT NULL, `contact_number` varchar(15) NOT NULL, `proof_of_identity` text NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_doctor – this table will store the information of the doctor who can access the records of the system.
- doctor_id – primary key of the table. It is set usually to auto increment (the database will automatically give this column a value starting from 1).
- first_name – first name of the doctor
- middle_name – middle name of the doctor
- last_name – last name of the doctor
- Specialization – specialization of the doctor
- complete_address – complete address of the doctor
- contact_number – contact number of the doctor, preferably mobile or cell phone number.
- proof_of_identity – photo of the doctor as proof of identity
- username – the desired username of the doctor, combined with the password to log in to the system
- password – the desired password of the doctor, combined with the username to log in to the system.
- status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
Create SQL Statement – the statement below is used to create the tbl_doctor, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_doctor` ( `doctor_id` int(11) NOT NULL, `first_name` varchar(30) NOT NULL, `middle_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `specialization` text NOT NULL, `complete_address` text NOT NULL, `contact_number` varchar(15) NOT NULL, `proof_of_identity` text NOT NULL, `username` varchar(30) NOT NULL, `password` varchar(30) NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_clinic – the details of the registered clinics in the system will be stored in this table.
- clinic_id – primary key of the table. It is set usually to auto increment (the database will automatically give this column a value starting from 1).
- doctor_id – this is a foreign key that points out to the doctor
- clinic_name – the name of the clinic
- clinic_permit – the business permit or permit to operate of the clinic
- complete_address – the complete address of where is the clinic located
- latitude – latitude measurement of the clinic’s location
- longitude – longitude measurement of the clinic’s location
- status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
Create SQL Statement – the statement below is used to create the tbl_clinic, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_clinic` ( `clinic_id` int(11) NOT NULL, `doctor_id` int(11) NOT NULL, `clinic_name` varchar(50) NOT NULL, `clinic_permit` text NOT NULL, `complete_address` text NOT NULL, `latitude` varchar(30) NOT NULL, `longitude` varchar(30) NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_clinic_services – this table will store the information of the services the clinic offers to register patients in the system.
- service_id – primary key of the table. It is set usually to auto increment (the database will automatically give this column a value starting from 1).
- clinic_id – this is a foreign key that points out to the clinic that provide the service
- doctor_id – this is a foreign key that points out the clinic’s doctor
- service_name- the name of the services offered by the clinic
- service_description – the description of the services offered
- amount – the amount to be paid for the service
Create SQL Statement – the statement below is used to create the tbl_clinic_services, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_clinic_services` ( `service_id` int(11) NOT NULL, `clinic_id` int(11) NOT NULL, `doctor_id` int(11) NOT NULL, `service_name` varchar(30) NOT NULL, `service_description` varchar(100) NOT NULL, `amount` float NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_clinic_schedule – this table store the information of the clinic’s schedule.
- schedule_id – primary key of the table. It is set usually to auto increment (the database will automatically give this column a value starting from 1).
- clinic_id – this is a foreign key that points out to the clinic
- date – the list of dates of the clinic’s schedule
- slots_available – the slots available to accommodate clinic appointment
Create SQL Statement – the statement below is used to create the tbl_clinic_schedule, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_clinic_schedule` ( `schedule_id` int(11) NOT NULL, `clinic_id` int(11) NOT NULL, `date` date NOT NULL, `slots_available` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_reservation – this table stores the information of the reservation or clinic appointments in the system.
- reservation_id – primary key of the table. It is set usually to auto increment (the database will automatically give this column a value starting from 1).
- appointment_code – a unique code given for the appointment.
- appointment_type – the type of appointment made by patients
- patient_id – this is a foreign key that points out to the patient that made the reservation.
- schedule_id – this is a foreign key that points out to the schedule of reservation.
- service_id – this is a foreign key that points out to the service reserved
- doctor_id – this is a foreign key that point out to the doctor assigned for the reservation
- amount – amount to be paid for the service
- remarks – comments and additional information about the reservation
- status – progress of the reservation, 0 is pending, 1 is accepted, 2 is cancelled.
Create SQL Statement – the statement below is used to create the tbl_reservation, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_reservation` ( `reservation_id` int(11) NOT NULL, `appointment_code` varchar(15) NOT NULL, `appointment_type` int(1) NOT NULL, `patient_id` int(11) NOT NULL, `schedule_id` int(11) NOT NULL, `service_id` int(11) NOT NULL, `doctor_id` int(11) NOT NULL, `amount` float NOT NULL, `remarks` varchar(100) NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_consultation – the records of patient consultation will be stored in this table.
- consultation_id -primary key of the table. It is set usually to auto increment (the database will automatically give this column a value starting from 1).
- appointment_code – a unique code given for the consultation.
- diagnosis – the diagnosis of the consultation made
- treatment – the treatment for the patient’s diagnosis
- upload_prescription – the prescription of the doctor for the patient.
- date_of_consultation – the date when the consultation was made
Create SQL Statement – the statement below is used to create the tbl_consultation, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_consultation` ( `consultation_id` int(11) NOT NULL, `appointment_code` varchar(15) NOT NULL, `diagnosis` varchar(100) NOT NULL, `treatment` varchar(100) NOT NULL, `upload_prescription` text NOT NULL, `date_of_consultation` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_admin_account – this table will store the information of the administrator’s account in the system.
- admin_id – primary key of the table. It is set usually to auto increment (the database will automatically give this column a value starting from 1).
- complete_name – complete name of the administrator.
- email -email address of of the administrator.
- contact – contact number of the patient, preferably mobile or cell phone number.
- username – username of the admin used to log in together with the password
- password – the password of the admin entered together with the user to access the system.
Create SQL Statement – the statement below is used to create the tbl_admin_account, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_admin_account` ( `admin_id` int(11) NOT NULL, `complete_name` varchar(100) NOT NULL, `email` varchar(50) NOT NULL, `contact` varchar(15) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Indexes for dumped tables
-- -- Indexes for table `tbl_admin_account` -- ALTER TABLE `tbl_admin_account` ADD PRIMARY KEY (`admin_id`); -- -- Indexes for table `tbl_clinic` -- ALTER TABLE `tbl_clinic` ADD PRIMARY KEY (`clinic_id`), ADD KEY `doctor_id` (`doctor_id`); -- -- Indexes for table `tbl_clinic_schedule` -- ALTER TABLE `tbl_clinic_schedule` ADD PRIMARY KEY (`schedule_id`), ADD KEY `clinic_id` (`clinic_id`); -- -- Indexes for table `tbl_clinic_services` -- ALTER TABLE `tbl_clinic_services` ADD PRIMARY KEY (`service_id`), ADD KEY `clinic_id` (`clinic_id`), ADD KEY `doctor_id` (`doctor_id`); -- -- Indexes for table `tbl_consultation` -- ALTER TABLE `tbl_consultation` ADD PRIMARY KEY (`consultation_id`); -- -- Indexes for table `tbl_doctor` -- ALTER TABLE `tbl_doctor` ADD PRIMARY KEY (`doctor_id`); -- -- Indexes for table `tbl_patient` -- ALTER TABLE `tbl_patient` ADD PRIMARY KEY (`patient_id`); -- -- Indexes for table `tbl_reservation` -- ALTER TABLE `tbl_reservation` ADD PRIMARY KEY (`reservation_id`), ADD KEY `patient_id` (`patient_id`), ADD KEY `schedule_id` (`schedule_id`), ADD KEY `service_id` (`service_id`), ADD KEY `doctor_id` (`doctor_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_admin_account` -- ALTER TABLE `tbl_admin_account` MODIFY `admin_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_clinic` -- ALTER TABLE `tbl_clinic` MODIFY `clinic_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_clinic_schedule` -- ALTER TABLE `tbl_clinic_schedule` MODIFY `schedule_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_clinic_services` -- ALTER TABLE `tbl_clinic_services` MODIFY `service_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_consultation` -- ALTER TABLE `tbl_consultation` MODIFY `consultation_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_doctor` -- ALTER TABLE `tbl_doctor` MODIFY `doctor_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_patient` -- ALTER TABLE `tbl_patient` MODIFY `patient_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_reservation` -- ALTER TABLE `tbl_reservation` MODIFY `reservation_id` int(11) NOT NULL AUTO_INCREMENT;
Summary
The app is needed because it is difficult to schedule appointments for multiple healthcare providers to stay on track with preventative care with the current healthcare system. Thousands of doctors are left without their primary physicians because of this issue, which means time wasted waiting for in-between visits. This is why apps like this are so important.
The first step in the systems development would be the proper planning of the storage part or layer of the project. That layer is the database design preparation and we are hoping we have provided you with the resources you need in developing your own version of clinic appointment system.
You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.
Hire our team to do the project.
Related links:
Blood Bank Information System Database Design
Lost and Found Information System Database Design
Daily Time Record System Database Design
Dairy Farm Management System Database Design
