PE Tools Management System Database Model

PE Tools Management System Database Model

This article will serve as a guide in designing your own tools management system. The database model is somewhat similar to the library system but instead of books, this project focuses on the inventory of PE tools and items.

Development Tools: the project is already available in Visual Basic and MS Access. You can also request our team to customize the project based on your preferred requirements and programming language.

tblaccount (account_id, account_name, username, password, can_add, can_delete, can_update, can_print, account_status)

Description: the system can be accessed by the administrator which has a full control and can access every module of the project, another account is the staff account is only limited to the encoding portion of the system. The tblaccount is the database table that stores information of the users that can access the system. Moreover the administrator can specify the action that the user can access such as the updating of records and printing of reports.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblaccount` (
`account_id` int(11) NOT NULL AUTO_INCREMENT,
`account_name` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`can_add` int(11) NOT NULL,
`can_delete` int(11) NOT NULL,
`can_update` int(11) NOT NULL,
`can_print` int(11) NOT NULL,
`account_status` int(11) NOT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblaccountlog (account_log_id, account_id, login_time, logout_time, date_recorded)

Description: tblaccount log is the database table that stores and records the login and logout time of the users.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblaccountlog` (
`account_log_id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
`login_time` time NOT NULL,
`logout_time` time NOT NULL,
`date_recorded` date NOT NULL,
PRIMARY KEY (`account_log_id`),
KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblborrowedlist (borrow_id, tool_id, borrower_id, date_borrowed, due_date, status, processed_by)

Description:  students, faculty and staff are allowed to borrow the items and tools available in the office. Every transaction are recorded and stored in the tblborrowedlist; information includes the tool information, borrower information the date of transaction and the user who processed the transaction.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblborrowedlist` (
`borrow_id` int(11) NOT NULL AUTO_INCREMENT,
`tool_id` int(11) NOT NULL,
`borrower_id` int(11) NOT NULL,
`date_borrowed` date NOT NULL,
`due_date` date NOT NULL,
`status` int(11) NOT NULL,
`processed_by` int(11) NOT NULL,
PRIMARY KEY (`borrow_id`),
KEY `tool_id` (`tool_id`,`borrower_id`,`processed_by`),
KEY `processed_by` (`processed_by`),
KEY `borrower_id` (`borrower_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblborrower (borrower_id, borrower_id_no, borrower_name, course_id, borrower_contact, borrower_email, encoded_by)

Description: tblborrower is the database table that stores the personal information of the students, faculty and staff. The system will require the borrower to provide information such as the id number, name, course or department, contact information and email address.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblborrower` (
`borrower_id` int(11) NOT NULL AUTO_INCREMENT,
`borrower_id_no` varchar(15) NOT NULL,
`borrower_name` varchar(100) NOT NULL,
`course_id` int(11) NOT NULL,
`borrower_contact` varchar(11) NOT NULL,
`borrower_email` varchar(50) NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`borrower_id`),
KEY `course_id` (`course_id`,`encoded_by`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcourse (course_id, course_name)

Description: courses and departments will also be encoded in the system and it will be stored in the tblcourse database table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcourse` (
`course_id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(100) NOT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblfineamount (fine_id, amount_of_fine)

Description: the fine amount refers to the amount that the borrower needs to pay if they forget to return the item on time.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblfineamount` (
`fine_id` int(11) NOT NULL AUTO_INCREMENT,
`amount_of_fine` float NOT NULL,
PRIMARY KEY (`fine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblfinerecord (fine_record_id, tool_id, borrower_id, fine_id, days_late, fine_amount, recorded_by)

Description: payment of fine is being kept and recorded for auditing purposes; it will be stored in the tblfinerecord table. Information stored in this table includes the tool or item info, borrower info, the amount to be paid and the user in-charge.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblfinerecord` (
`fine_record_id` int(11) NOT NULL AUTO_INCREMENT,
`tool_id` int(11) NOT NULL,
`borrower_id` int(11) NOT NULL,
`fine_id` int(11) NOT NULL,
`days_late` int(11) NOT NULL,
`fine_amount` float NOT NULL,
`recorded_by` int(11) NOT NULL,
PRIMARY KEY (`fine_record_id`),
KEY `tool_id` (`tool_id`,`borrower_id`,`fine_id`,`recorded_by`),
KEY `fine_id` (`fine_id`),
KEY `borrower_id` (`borrower_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbltool (tool_id, tool_name, tool_barcode, qty_on_hand, encoded_by)

Description: PE tools and items will be encoded in the system and it will be stored in the tbltool database table. Information included the name of the item, barcode if applicable and the quantity on hand.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbltool` (
`tool_id` int(11) NOT NULL AUTO_INCREMENT,
`tool_name` varchar(100) NOT NULL,
`tool_barcode` varchar(25) NOT NULL,
`qty_on_hand` int(4) NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`tool_id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables
—
— Constraints for table `tblaccountlog`
—
ALTER TABLE `tblaccountlog`
ADD CONSTRAINT `tblaccountlog_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `tblaccount` (`account_id`) ON DELETE CASCADE;

—
— Constraints for table `tblborrowedlist`
—
ALTER TABLE `tblborrowedlist`
ADD CONSTRAINT `tblborrowedlist_ibfk_3` FOREIGN KEY (`borrower_id`) REFERENCES `tblborrower` (`borrower_id`) ON DELETE CASCADE,
ADD CONSTRAINT `tblborrowedlist_ibfk_1` FOREIGN KEY (`processed_by`) REFERENCES `tblaccount` (`account_id`) ON DELETE CASCADE,
ADD CONSTRAINT `tblborrowedlist_ibfk_2` FOREIGN KEY (`tool_id`) REFERENCES `tbltool` (`tool_id`) ON DELETE CASCADE;

—
— Constraints for table `tblborrower`
—
ALTER TABLE `tblborrower`
ADD CONSTRAINT `tblborrower_ibfk_2` FOREIGN KEY (`encoded_by`) REFERENCES `tblaccount` (`account_id`) ON DELETE CASCADE,
ADD CONSTRAINT `tblborrower_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`) ON DELETE CASCADE;

—
— Constraints for table `tblfinerecord`
—
ALTER TABLE `tblfinerecord`
ADD CONSTRAINT `tblfinerecord_ibfk_3` FOREIGN KEY (`tool_id`) REFERENCES `tbltool` (`tool_id`) ON DELETE CASCADE,
ADD CONSTRAINT `tblfinerecord_ibfk_1` FOREIGN KEY (`fine_id`) REFERENCES `tblfineamount` (`fine_id`) ON DELETE CASCADE,
ADD CONSTRAINT `tblfinerecord_ibfk_2` FOREIGN KEY (`borrower_id`) REFERENCES `tblborrower` (`borrower_id`) ON DELETE CASCADE;

—
— Constraints for table `tbltool`
—
ALTER TABLE `tbltool`
ADD CONSTRAINT `tbltool_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tblaccount` (`account_id`) ON DELETE CASCADE;

Development Tools: Visual Basic and MS Access/MySQL/MariaDB

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.