Procurement Management System Database Design
This article will serve as a guide in the development of procurement management system and related systems to procurement. The article contains the list of database tables used in our version of the procurement system.
Database Design/Schema/Model and List of DB Tables
tbldepartment (dept_id, dept_initial, full_description, encoded_by, encoded_date)
Description: tbldepartment is the database table that stores the information of the different offices and department of the company. The table has 5 entities; (1) dept_id is the primary key of the table, (2) dept_initial refers to the shortcut name or code of every office, (3) full_description is the full name of the office or department, (4) encoded_by is the user or staff that is responsible for encoding the department information, (5) encoded_date is the date of data entry to the system.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tbldepartment` (
`dept_id` int(11) NOT NULL AUTO_INCREMENT,
`dept_initial` varchar(15) DEFAULT NULL,
`full_description` varchar(100) DEFAULT NULL,
`encoded_by` int(11) DEFAULT NULL,
`encoded_date` date DEFAULT NULL,
PRIMARY KEY (`dept_id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblitem (item_id, item_name, unit_id, unit_price, category_id, encoded_by, encoded_date)
Description: this table is used for storing the records of the different items that will be included in the annual procurement plan. The table has 7 entities; (1) item_id is the primary key, (2) item_name is the name of the product or item, (3) unit_id is a foreign key that links to the tblunit table, it refers or it will determine the quantity of the item, (4) unit_price is the price of the item per unit, (5) category_id is also a foreign key that links to the tblcategory, it is used to classify and group the products, (6) encoded_by is also a foreign key that links to the tbluser, it refers to the one who encodes the item, (7)encoded_date is the date of data entry to the system.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblitem` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_name` varchar(30) DEFAULT NULL,
`unit_id` int(11) DEFAULT NULL,
`unit_price` float DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
`encoded_by` int(11) DEFAULT NULL,
`encoded_date` date DEFAULT NULL,
PRIMARY KEY (`item_id`),
KEY `unit_id` (`unit_id`),
KEY `encoded_by` (`encoded_by`),
KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblitemcategory (category_id, category_name)
Description: tblitemcategory is the table that records the different groups and classification of products.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblitemcategory` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(30) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblprocurementplan (plan_id, item_id, unit_price, quantity_1st_quarter, status_1st_quarter, quantity_2nd_quarter, status_2nd_quarter, quantity_3rd_quarter, status_3rd_quarter, quantity_4th_quarter, status_4th_quarter, total_quantity, total_amount, dept_id, requested_by, remarks, school_year, date_encoded, locked, plan_status)
Description: tblprocurementplan is the database table that stores the list of item that is part of the annual procurement plan of the department or office. Every office will need to identify the list of items (office supplies and equipment) that will be used in their operation for the entire year. The request will be processed per quarter.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblprocurementplan` (
`plan_id` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(11) DEFAULT NULL,
`unit_price` float DEFAULT NULL,
`quantity_1st_quarter` int(6) DEFAULT NULL,
`status_1st_quarter` varchar(30) DEFAULT NULL,
`quantity_2nd_quarter` int(6) DEFAULT NULL,
`status_2nd_quarter` varchar(30) DEFAULT NULL,
`quantity_3rd_quarter` int(6) DEFAULT NULL,
`status_3rd_quarter` varchar(30) DEFAULT NULL,
`quantity_4th_quarter` int(6) DEFAULT NULL,
`status_4th_quarter` varchar(30) DEFAULT NULL,
`total_quantity` int(12) NOT NULL,
`total_amount` double DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
`requested_by` varchar(100) DEFAULT NULL,
`remarks` varchar(100) DEFAULT NULL,
`school_year` int(4) DEFAULT NULL,
`date_encoded` date DEFAULT NULL,
`locked` char(1) NOT NULL,
`plan_status` varchar(50) NOT NULL,
PRIMARY KEY (`plan_id`),
KEY `dept_id` (`dept_id`,`item_id`),
KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblpurchaseorder (order_id, po_no, date, mode_of_payment, supplier, supplier_address, requesting_office, processed_by, requested_by, po_status, date_processed)
Description: purchase orders will be stored in the tblpurchaseorder table. According to Wikipedia; A purchase order (PO) is a commercial document and first official offer issued by a buyer to a seller indicating types, quantities, and agreed prices for products or services. It is used to control the purchasing of products and services from external suppliers. The table has 11 entities; (1) order_id is the primary key of the table, (2) po_no is the purchase order number, this is the control number of the transaction, (3) date the date of purchase order was created, (4) mode_of_payment refers to the type of payment to the supplier, (5) supplier is the one who will supply the list of items or equipment, (6) supplier_address it refers to the company address of the supplier, (7) requesting_office is the department who requested the purchase of the items, (8) processed_by is the user who encodes and processed the transaction, (9) requested_by refers to the head of the office or department who requested the purchase of the items, (10) status of the transaction (approved or on-going), (11) date_processed the processing date of the purchase order.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblpurchaseorder` (
`po_no` varchar(25) NOT NULL DEFAULT ”,
`date` date DEFAULT NULL,
`mode_of_payment` varchar(50) DEFAULT NULL,
`supplier` varchar(50) DEFAULT NULL,
`supplier_address` varchar(100) DEFAULT NULL,
`requesting_office` int(11) DEFAULT NULL,
`processed_by` int(11) DEFAULT NULL,
`requested_by` varchar(100) NOT NULL,
`po_status` varchar(30) NOT NULL,
`date_processed` date NOT NULL,
PRIMARY KEY (`po_no`),
KEY `requesting_office` (`requesting_office`),
KEY `processed_by` (`processed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tblpurchaseorderdetail (id, po_no, item_id, quantity, unit_cost, total_amount, quarter)
Description: tblpurchaseorderdetail is the table that stores the list of items that is included in the purchase order transaction. It has 7 entities; (1) id is the primary key of the table, (2) po_no is the reference key or foreign key that connects to the tblpurchaseorder table, (3) item_id is the foreign key that links to the tblitem, (4) quantity refers to the number of orders per item, (5) unit_cost is the price of the supplier per item, (6) total_amount is equal to quantity of item multiple to the amount, (7) quarter refers to the (1st to 4th quarter of the year)
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblpurchaseorderdetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`po_no` varchar(25) DEFAULT NULL,
`item_id` int(11) NOT NULL,
`quantity` float DEFAULT NULL,
`unit_cost` float DEFAULT NULL,
`total_amount` float DEFAULT NULL,
`quarter` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `po_no` (`po_no`),
KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblpurchaserequest (pr_no, purpose, requested_by, date_processed, processed_by, department_id, status)
Description: A purchase request is a request from a department to the procurement area for purchasing a specific item or service. For goods, it must contain a full detailed description, the unit of measurement and quantity. Source: https://specialties.bayt.com/en/specialties/q/111161/what-is-the-meaning-of-purchase-request-and-purchase-order/
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblpurchaserequest` (
`pr_no` varchar(15) NOT NULL DEFAULT ”,
`purpose` varchar(100) DEFAULT NULL,
`requested_by` varchar(100) DEFAULT NULL,
`date_processed` date DEFAULT NULL,
`processed_by` int(11) DEFAULT NULL,
`department_id` int(11) NOT NULL,
`status` varchar(15) NOT NULL,
PRIMARY KEY (`pr_no`),
KEY `processed_by` (`processed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tblpurchaserequestdetail (id, pr_no, item_id, quantity, estimated_amount, estimated_cost, quarter)
Description: the same with the purchase order, the list of items in the purchase request transaction are stored in a separate table and it will be stored in the tblpurchaserequestdetail
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblpurchaserequestdetail` (
`id` int(11) NOT NULL,
`pr_no` varchar(15) NOT NULL,
`item_id` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`estimated_amount` float DEFAULT NULL,
`estimated_cost` float DEFAULT NULL,
`quarter` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pr_no` (`pr_no`,`item_id`),
KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tblunit (unit_id, unit_name, description)
Description: unit refers to the measurement of the items such as piece, box, kilo, etc.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblunit` (
`unit_id` int(11) NOT NULL AUTO_INCREMENT,
`unit_name` varchar(15) NOT NULL,
`description` varchar(30) NOT NULL,
PRIMARY KEY (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tbluser (user_id, full_name, contact, designation, department_id, user_category, username, password)
Description: authorized users can only accessed the system, that is why employees that will be involve in the processing and purchasing of items must register their personal information in the system. The information of the employees will be stored in the tbluser table.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`full_name` varchar(50) DEFAULT NULL,
`contact` varchar(11) DEFAULT NULL,
`designation` varchar(30) DEFAULT NULL,
`department_id` int(11) DEFAULT NULL,
`user_category` int(1) DEFAULT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`user_id`),
KEY `department_id` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblvoucher (id, voucher_no, po_no, total_amount, bir_percentage, bir_amount, income_percentage, income_amount, net_amount, extra_expense, due_bir, cash_in_bank, processed_by, date_processed)
Description: A document which can be used as proof that a monetary transaction has occurred between two parties. In business, a payment voucher can be used for a variety of purposes, sometimes taking the place of cash in a transaction, acting as a receipt, or indicating that an invoice has been approved for payment. Source: http://www.businessdictionary.com/definition/payment-voucher.html
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblvoucher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`voucher_no` varchar(255) DEFAULT NULL,
`po_no` varchar(25) NOT NULL,
`total_amount` decimal(16,3) DEFAULT NULL,
`bir_percentage` int(11) DEFAULT NULL,
`bir_amount` float DEFAULT NULL,
`income_percentage` float DEFAULT NULL,
`income_amount` float DEFAULT NULL,
`net_amount` float DEFAULT NULL,
`extra_expense` float DEFAULT NULL,
`due_bir` varchar(30) DEFAULT NULL,
`cash_in_bank` float DEFAULT NULL,
`processed_by` int(11) NOT NULL,
`date_processed` date NOT NULL,
PRIMARY KEY (`id`),
KEY `po_no` (`po_no`),
KEY `processed_by` (`processed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Constraints for dumped tables
—
— Constraints for table `tbldepartment`
—
ALTER TABLE `tbldepartment`
ADD CONSTRAINT `tbldepartment_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
—
— Constraints for table `tblitem`
—
ALTER TABLE `tblitem`
ADD CONSTRAINT `tblitem_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `tblitemcategory` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblitem_ibfk_1` FOREIGN KEY (`unit_id`) REFERENCES `tblunit` (`unit_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblitem_ibfk_2` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
—
— Constraints for table `tblprocurementplan`
—
ALTER TABLE `tblprocurementplan`
ADD CONSTRAINT `tblprocurementplan_ibfk_2` FOREIGN KEY (`dept_id`) REFERENCES `tbldepartment` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblprocurementplan_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `tblitem` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE;
—
— Constraints for table `tblpurchaseorder`
—
ALTER TABLE `tblpurchaseorder`
ADD CONSTRAINT `tblpurchaseorder_ibfk_2` FOREIGN KEY (`processed_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpurchaseorder_ibfk_1` FOREIGN KEY (`requesting_office`) REFERENCES `tbldepartment` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE;
—
— Constraints for table `tblpurchaseorderdetail`
—
ALTER TABLE `tblpurchaseorderdetail`
ADD CONSTRAINT `tblpurchaseorderdetail_ibfk_2` FOREIGN KEY (`po_no`) REFERENCES `tblpurchaseorder` (`po_no`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpurchaseorderdetail_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `tblitem` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE;
—
— Constraints for table `tblpurchaserequest`
—
ALTER TABLE `tblpurchaserequest`
ADD CONSTRAINT `tblpurchaserequest_ibfk_1` FOREIGN KEY (`processed_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
—
— Constraints for table `tblpurchaserequestdetail`
—
ALTER TABLE `tblpurchaserequestdetail`
ADD CONSTRAINT `tblpurchaserequestdetail_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `tblitem` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpurchaserequestdetail_ibfk_1` FOREIGN KEY (`pr_no`) REFERENCES `tblpurchaserequest` (`pr_no`) ON DELETE CASCADE ON UPDATE CASCADE;
—
— Constraints for table `tblvoucher`
—
ALTER TABLE `tblvoucher`
ADD CONSTRAINT `tblvoucher_ibfk_2` FOREIGN KEY (`processed_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblvoucher_ibfk_1` FOREIGN KEY (`po_no`) REFERENCES `tblpurchaseorder` (`po_no`) ON DELETE CASCADE ON UPDATE CASCADE;
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.
