Pharmacy Stocks Management System Database Design

Pharmacy Stocks Management System Database Design

Managing pharmacy stocks may be a hard task, especially when it comes to keeping track of inventory levels, acquiring new stock, and ensuring that all drugs are stored and given safely. Using a Pharmacy Stocks Management System, which may be improved even further with a well-designed database, is one approach to simplify this procedure. In this article, we’ll talk about the value of database design in creating a Pharmacy Stocks Management System and how it may make pharmacies run more smoothly.

About the Project

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.

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.

Importance of Good Database Design in Software Development

Any software system that manages pharmacy supplies must have a good database design, but it is especially crucial for these systems. Such systems must be capable of handling a sizable amount of medication-related data, including drug names, dosages, expiration dates, and manufacturer details. They also need to be able to conduct extensive sales, procurement, and inventory management transactions. A well-designed database ensures that all of this data can be stored, accessed, and changed efficiently and precisely.

One of the key benefits of good database design in pharmacy stocks management systems is improved data quality and consistency. A well-designed database enforces data integrity rules, such as unique constraints and referential integrity, which help prevent data duplication, inconsistencies, and errors. This is critical in the context of medication management, where even small mistakes can have serious consequences for patient health and safety. In addition, a good database design enables the system to generate accurate and timely reports on medication stock levels, expiration dates, and other critical information. This information can be used by pharmacists and other healthcare professionals to make informed decisions about medication ordering, dispensing, and usage, ultimately improving patient outcomes.

Database Tables

tbl_user – this table will store the information of the users in 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 username of the user combined with the password to login to the system.
• password – the password of the user combined with the username to login to the system.
• complete_name – complete name of the user.
• usertype – 0-encoder, 1-admin

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(50) NOT NULL,
`password` varchar(50) NOT NULL,
`complete_name` varchar(100) NOT NULL,
`usertype` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_medicine_type – the types of the medicine 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 type of medicine.
• created_date – the date when the medicine type was encoded
• 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(50) NOT NULL,
`created_date` date NOT NULL,
`created_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_medicine_category – this table will store the information of the medicine categories in the system.

• 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 medicine category
• created_date – the date when the medicine category record was made
• 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(255) NOT NULL,
`created_date` date NOT NULL,
`created_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_supplier – this table will store the information of the suppliers 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
• details – the details about the supplier
• address – the address of the supplier
• contact_info – the contact information about the supplier
• status – status of the supplier, active or inactive
• created_date – the date when the supplier record was made
• created_by (user_id) – this is a foreign key that points out to the user

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(255) NOT NULL,
`details` text DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`contact_info` varchar(255) DEFAULT NULL,
`status` enum('active','inactive') NOT NULL DEFAULT 'active',
`created_date` date NOT NULL,
`created_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_medicine – the details of the medicines will be stored in this table.

• 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 – batch number f the medicine supplies
• name – the name of the medicine
• specification – specification of the medicines
• category_id – this is a foreign key that points out to the category of medicine
• type_id – this is a foreign key that points out to the type medicine
• measurement – measurement of the medicine
• remarks – additional information about the medicine
• price – the price of the medicine
• retail_price – the retail price of the medicine
• quantity_on_hand – the available quantity of medicine in stock
• expiry_date – the date when the medicine will be expired
• created_by (user_id) – this is a foreign key that points out to the user who created the medicine 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(50) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`specification` varchar(255) DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
`type_id` int(11) DEFAULT NULL,
`measurement` varchar(50) DEFAULT NULL,
`remarks` varchar(255) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`retail_price` decimal(10,2) DEFAULT NULL,
`quantity_on_hand` int(11) DEFAULT NULL,
`expiry_date` date DEFAULT NULL,
`created_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_receiving – this table store the information of the medicines received in the pharmacy

• 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 – batch number of the 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
• quantity – this is the number of medicines received
• amount – the amount paid for the medicine’s received
• remarks – additional information about the medicine received
• reference_no – reference number for the medicine received
• date_received – the date when the medicine was received
• processed_by (user_id) – this is a foreign key that points out to the user who processed the receiving transaction

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(50) DEFAULT NULL,
`supplier_id` int(11) DEFAULT NULL,
`medicine_id` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`remarks` text DEFAULT NULL,
`reference_no` varchar(50) DEFAULT NULL,
`date_received` date DEFAULT NULL,
`processed_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_return – this table will store the information of the medicines returned to the supplier.

• 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 medicines returned
• 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
• quantity- the number of medicines returned
• amount – the amount of medicines returned
• remarks – additional information about the medicines returned
• reference_no – reference number of the medicines returned
• date_processed – the date when the medicines was returned
• processed_by (user_id) – this is a foreign key that points out to the user who processed the returned medicines

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(50) DEFAULT NULL,
`supplier_id` int(11) DEFAULT NULL,
`medicine_id` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`remarks` text DEFAULT NULL,
`reference_no` varchar(50) DEFAULT NULL,
`date_processed` date DEFAULT NULL,
`processed_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_stock_out – this table stores the information of the medicine stock out.

• 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).
• medicine_id – this is a foreign key that points out to the medicine
• quantity – the number of medicine stock out
• amount – the amount of the medicine stock out
• remarks – additional information about the medicine stock out
• reference_no -unique reference number for the medicine stock out
• date_processed – the date when the medicine stock out was processed.
• processed_by (user_id) – this is a foreign key that points out to the user who processed the medicine stock out.

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,
`medicine_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`remarks` text DEFAULT NULL,
`reference_no` varchar(255) DEFAULT NULL,
`date_processed` date NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

FREE DOWNLOAD DATABASE

Summary

The blog post on Pharmacy Stocks Management System Database Design provides a comprehensive overview of the database design for a system that manages pharmacy stocks. It covers the various tables that need to be created, their attributes, and how they are related to each other. The blog post includes SQL statements for creating tables, and it explains how they can be used to manage the inventory of medicines, suppliers, and stock in and out. The post also discusses some of the key considerations for designing a robust and efficient database, such as data normalization and the use of foreign keys. Overall, this blog post is a useful resource for anyone interested in developing a database for a pharmacy stocks management system.

Please watch the video tutorial that will be posted on our YouTube Channel.

Readers are also interested in:

Export database record into csv file in PHP and MySQL

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.