Pharmacy Stocks Management System Database Design
Introduction
Table of Contents
The capstone project “Pharmacy Stocks Management System” allows pharmacies to manage and monitor their stocks of drugs electronically. The Pharmacy Stocks Management System will automate inventory to help ensure that the pharmacy has enough stock of medications and supplies to serve the needs of the patients.
Pharmacy is where patients buy drugs or medicines for medication. It is important for the pharmacies to ensure that they have adequate stocks of the drugs to provide for the needs of the patients. Pharmacies do inventory to monitor their stocks however pharmacies do their inventory manually which is time consuming and may also encounter human errors. What pharmacies need is an automated system to help them manage their stocks efficiently and ensure that they have adequate stocks for the inquiries of the patients.
It is possible to manage a pharmacy’s inventory through the use of a web-based program, such as the Pharmacy Stocks Management System. Users can keep track of the stock levels of pharmaceuticals and other medical supplies, and they can place orders for additional supplies as needed. Users can also generate reports on the status of pharmaceuticals and other medical supplies in stock, which is a convenient feature. User-friendliness has been prioritized in the development of the system, which can be accessed from any computer with internet access.
Among the many features of the Pharmacy Stocks Management System are the following:
- Stock management system for a pharmacy
- Tracking of inventory levels of medications and other medical supplies
- Generating reports on the stock levels of medications and other medical supplies
- User-friendly design
- Accessible from any computer with internet access.
Database Tables
This article will provide you with an idea about the pharmacy management system database design.
tbl_user – this table will store the information of the users 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 user combined with the password to login to the system
- password – the desired password of the user for his/her account also used to log in to his/her account
- complete_name – the complete name of the user
- user_type – (0)admin,(1)encoder
- account_status – (0)active,(1)inactive
Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_user` ( `user_id` int(11) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `complete_name` varchar(100) NOT NULL, `user_type` int(1) NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_medicine_type – the medicine type information will be stored in this table.
- type_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).
- type_name – the name of the specific medicine type
- created_date – the date the medicine type record was created
- created_by(user_id) – this is a foreign key that points out to the user who created the record
Create SQL Statement – the statement below is used to create the tbl_medicine_type, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_medicine_type` ( `type_id` int(11) NOT NULL, `type_name` varchar(30) NOT NULL, `created_date` date NOT NULL, `created_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_medicine_category – the medicine category information will be stored in this table.
- category_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).
- category_name-the name of the specific medicine category
- created_date – the date the medicine category record was created
- created_by(user_id) – this is a foreign key that points out to the user who created the record
Create SQL Statement – the statement below is used to create the tbl_medicine_category, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_medicine_category` ( `category_id` int(11) NOT NULL, `category_name` varchar(30) NOT NULL, `created_date` date NOT NULL, `created_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_medicine – this table will store the information of the medicines recorded in the system.
- medicine_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).
- batch_no – unique number assigned to a specific batch of medicines
- name – the name of the medicine
- specification – the medicine’s specifications and for what use it is
- category_id – this is a foreign key that points out to the medicine category
- type_id – this is a foreign key that points out to the medicine type
- supplier_id – this is a foreign key that points out to the supplier of the medicine
- measurement – the measurement of the medicine
- remarks – additional information about the medicine
- price – the price of the medicine
- retail_price – the price of the medicine for retail
- quantity_on_hand – the number of medicine stock on hand
- expiry_date -the expiry date of the medicines
- created_by – the user who created the record
Create SQL Statement – the statement below is used to create the tbl_medicine, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_medicine` ( `medicine_id` int(11) NOT NULL, `batch_no` varchar(15) NOT NULL, `name` varchar(50) NOT NULL, `specification` varchar(100) NOT NULL, `category_id` int(11) NOT NULL, `type_id` int(11) NOT NULL, `supplier_id` int(11) NOT NULL, `measurement` varchar(15) NOT NULL, `remarks` varchar(100) NOT NULL, `price` float NOT NULL, `retail_price` float NOT NULL, `quantity_on_hand` int(6) NOT NULL, `expiry_date` date NOT NULL, `created_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_supplier – this table will store the information of the suppliers registered in the system.
- supplier_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).
- supplier_name – the name of the supplier
- description -description about the supplier
- status – (0)active,(1)inactive
- created_date the date when the record of the supplier was created
- created_by(user_id) – this is a foreign key that points out to the user who made the record
- last_modified_date – the date when was the record last modified
Create SQL Statement – the statement below is used to create the tbl_supplier, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_supplier` ( `supplier_id` int(11) NOT NULL, `supplier_name` varchar(50) NOT NULL, `description` varchar(100) NOT NULL, `status` int(1) NOT NULL, `created_date` date NOT NULL, `created_by` int(11) NOT NULL, `last_modified_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_receiving – this table will store the information of the medicine supplies received.
- receiving_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).
- batch_no – the number assigned to the batch of medicines received
- supplier_id – this is a foreign key that points out to the supplier
- medicine_id – this is a foreign key that points out to the medicine
- price – the price of the medicines received
- quantity – the quantity of the medicines received
- amount – the amount of the medicines received
- remarks – additional information about the medicines received
- reference_no – the unique number given to the medicines received for reference
- date_received – the date the medicines was received
- processed_by(user_id) – this is a foreign key that points out to the user who processed the record
Create SQL Statement – the statement below is used to create the tbl_receiving, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_receiving` ( `receiving_id` int(11) NOT NULL, `batch_no` varchar(30) NOT NULL, `supplier_id` int(11) NOT NULL, `medicine_id` int(11) NOT NULL, `price` float NOT NULL, `quantity` int(6) NOT NULL, `amount` float NOT NULL, `remarks` varchar(50) NOT NULL, `reference_no` varchar(30) NOT NULL, `date_received` date NOT NULL, `processed_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_return – this table store the information of the returned medicines.
- return_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).
- batch_no – batch number of the returned medicines
- supplier_id – this is a foreign key that points out to the supplier
- medicine_id – this is a foreign key that points out to the medicines
- price – the price of the medicines
- quantity – the quantity of the medicines returned
- amount- the amount of the medicines returned
- remarks – additional information about the medicines returned
- reference_no- this is a unique reference number given to the medicines for return
- date_returned – the date the returned medicine was received
- processed_by(user_id) – this is a foreign key that points out to the user who processed the medicine returns
Create SQL Statement – the statement below is used to create the tbl_return, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_return` ( `return_id` int(11) NOT NULL, `batch_no` varchar(15) NOT NULL, `supplier_id` int(11) NOT NULL, `medicine_id` int(11) NOT NULL, `price` float NOT NULL, `quantity` int(6) NOT NULL, `amount` float NOT NULL, `remarks` varchar(50) NOT NULL, `reference_no` varchar(30) NOT NULL, `date_returned` date NOT NULL, `processed_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_stock_out – the medicine stock out details will be stored in this table.
- stock_out_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).
- batch_no – the number of the batch assigned for the medicine stock out
- supplier_id – this is a foreign key that points out to the supplier
- medicine_id – this is a foreign key that points out to the medicine
- price – the price of the medicines
- quantity – the number of medicine stock out
- amount – the amount of the medicines
- reference_no- the reference number of the medicines
- date_processed – the date the record was processed
- processed_by(user_id) – this is a foreign key that points out to the user who processed the record
Create SQL Statement – the statement below is used to create the tbl_stock_out, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_stock_out` ( `stock_out_id` int(11) NOT NULL, `batch_no` varchar(15) NOT NULL, `supplier_id` int(11) NOT NULL, `medicine_id` int(11) NOT NULL, `price` float NOT NULL, `quantity` int(6) NOT NULL, `amount` float NOT NULL, `reference_no` varchar(30) NOT NULL, `date_processed` date NOT NULL, `processed_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_request – the medicine request records will be stored in this table.
- request_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).
- medicine_id – this is a foreign key that points out to the medicine
- supplier_id – this is a foreign key that points out to the supplier
- measurement – the measurement of the medicine requested
- price – the price of the medicines
- quantity – the quantity of the medicines requested
- amount – the amount of the requested medicines
- date_processed – the date the medicine request was processed
- processed_by(user_id) – this is a foreign key that points out to the user who process the request,
Create SQL Statement – the statement below is used to create the tbl_request, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_request` ( `request_id` int(11) NOT NULL, `medicine_id` int(11) NOT NULL, `supplier_id` int(11) NOT NULL, `measurement` varchar(15) NOT NULL, `price` float NOT NULL, `quantity` int(6) NOT NULL, `amount` float NOT NULL, `date_processed` date NOT NULL, `processed_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Video Tutorial
Summary
In this article, we have identified the list of tables that should be included in the development of the project on pharmacy stocks management system. The design planning stage of a database project is the stage during which the overall design of the database is developed. It is customary for this stage to begin with the development of a data model, which is a graphical representation of the information to be kept in the database. This model is used to determine the structure of the database and the links between different pieces of information. During the design planning step, you will also make a decision on a database platform and construct architecture for your application.
Please watch the video tutorial on how to prepare and create the different tables of the database of Pharmacy Stocks Management 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 and Articles:
Point of Sale System for Pharmacy in C# and SQL Server
Pharmacy Stocks Management System Free Template
Hospital Management System in PHP and MySQL
Web and Mobile Medicine Information System Capstone Project
