Transcribe Medical System Database Design
About the System
Table of Contents
Information technology has made everything easier and simpler, including transcribing the medical diagnosis of patients. The capstone project, entitled “Medical Transcription Platform,” is designed to allow medical transcriptionists to transcribe audio of medical consultations and diagnose patients in a centralized manner. A medical transcriptionist is vital to keep accurate and credible medical records of patients and can be used by other doctors to know the patients’ medical history. The said project will serve as a platform where transcribed medical audios are stored for safekeeping and easy retrieval.
Patients’ medical records and history is vital in the process of future patient diagnosis and reference of health professionals. Hospitals keep records of the patient’s result of medical consultations and previous diagnosis; one of the recording methods is through medical transcription. Medical transcription is the process of recording all patient’s details of previous and existing ailments, results of different laboratories, and other records into an audio file. The doctors, nurses, and other health physicians need to easily access and retrieve the audio files to support them with accurate medical records of patients for future diagnosis. Hospitals and medical transcriptionists need a centralized platform to store the transcribed audios and at the same time give access to authorized personnel for retrieval.
In this article/video tutorial, we will discuss the database design of a Transcribe Medical System. Let’s get started with creating the tables and their columns. Please watch the video for a more in-depth demonstration.
Database Tables
Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.
tbl_dialect – this table will hold the information if the dialects available in the system.
- dialect_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).
- language_name – name of the language
- description – information about the language
Create SQL Statement – the statement below is used to create the tbl_dialect, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_dialect` ( `dialect_id` int(11) NOT NULL, `language_name` varchar(30) NOT NULL, `description` varchar(150) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_member – the member’s information will be stored in this table.
- member_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).
- id_number – unique id number of the members
- first_name – the member’s first name
- middle_name – the member’s middle name
- last_name – the member’s last name
- gender- the gender of the member
- address- the complete address of the member
- contact – the contact details of the member preferably cellphone or mobile number
- email_address – email address of the member
- profile_picture – this will hold the profile picture of the member
- username – the desired username of the member, combined with the password to login to the system
- password – the desired password of the member for his/her account
- account_status – (0)pending, (1)approved, (2)cancelled
- user_id – managed by
Create SQL Statement – the statement below is used to create the tbl_member, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_member` ( `member_id` int(11) NOT NULL, `id_number` varchar(15) NOT NULL, `first_name` varchar(30) NOT NULL, `middle_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `gender` int(1) NOT NULL, `address` text NOT NULL, `contact` varchar(15) NOT NULL, `email_address` varchar(50) NOT NULL, `profile_picture` longblob NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_audio – this table will hold the information of the transcribed audio in the system. This table is also the source of the report for the member session analytics and number of audio files uploaded per month.
- audio_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).
- member_id – this is a foreign key that points out to the member
- file_name – the file name of the audio
- description – description or information about the audio
- file_size – the size of the audio file
- duration – duration of the audio file
- dialect_id – this is a foreign key that points out to the dialect used
- status – (0)processing, (1)completed
- transcript_conversation – the record of the transcript conversation
- date – date when the audio was transcribed
Create SQL Statement – the statement below is used to create the tbl_audio, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_audio` ( `audio_id` int(11) NOT NULL, `member_id` int(11) NOT NULL, `file_name` varchar(50) NOT NULL, `description` varchar(100) NOT NULL, `file_size` double NOT NULL, `duration` varchar(15) NOT NULL, `dialect_id` int(11) NOT NULL, `status` int(1) NOT NULL, `transcript_conversation` text NOT NULL, `date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_upload_setting – this table will hold the information of the upload setting of the audios.
- setting_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).
- max_size_allowed -maximum size of the audio allowed to be uploaded
- file_type_allowed – the type of file allowed to be uploaded
- enable_sms – (0)on, (1)off
- updated_by – user_id – this is a foreign key that points out to the user who commit the update
Create SQL Statement – the statement below is used to create the tbl_upload_setting, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_upload_setting` ( `setting_id` int(11) NOT NULL, `max_size_allowed` double NOT NULL, `file_type_allowed` text NOT NULL, `enable_sms` int(1) NOT NULL, `updated_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_sms_config – the details of the sms configuration will be held in this table. The project will the API of iTexMo SMS.
- config_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).
- api_code – unique API code
- api_password – the API password
- updated_by – user_id – this is a foreign key that points out to the user who commit the update
Create SQL Statement – the statement below is used to create the tbl_sms_config, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_sms_config` ( `config_id` int(11) NOT NULL, `api_code` varchar(30) NOT NULL, `api_password` varchar(100) NOT NULL, `updated_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_admin_profile – this table will store the information of the admin profile of the system.
- user_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).
- username – the preferred username of the admin
- password- the preferred password of the admin of the system for the administrator’s account
- email_address -email address of the admin
- contact_number – contact details of the admin, preferably cellphone or mobile number
- avatar – this will hold the profile photo of the admin
Create SQL Statement – the statement below is used to create the tbl_admin_profile, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_admin_profile` ( `user_id` int(11) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `email_address` varchar(50) NOT NULL, `contact_number` varchar(15) NOT NULL, `avatar` longblob NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Video Tutorial
Summary
It is one of the most critical components of software development to have a well-designed database. In order for software to perform properly and efficiently, it is important that the database be well-designed. Database inconsistency, data corruption, and other software failures are all possible outcomes of a poorly constructed database.
The Transcribe Medical System is a medical transcription software program that assists medical practitioners in the transcription of medical reports and reports from other sources. It is a straightforward piece of software that is simple to understand and utilize. The software aids in the improvement of medical transcription accuracy as well as the improvement of the overall efficiency of the medical transcription process. Additionally, it contributes to the improvement of the quality of the medical transcribing service.
We hope that this content will help you in designing a database for your own version of Transcribe Medical System or related to it. Please watch the video tutorial that will be posted on our YouTube Channel.
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 Topics and Articles:
IPO Model Conceptual Framework of Transcribe Medical – Medical Speech to Text Converter
Online Medical Record System Free Source code in Bootstrap
Medical Transcription Platform Free Template Source code
Online Consultation and Medical Subscription
