Asset Management System Database Model

Asset Management System Database Model

The project entitled asset management system will only focus on the physical assets of the company such as the computers, automobiles, etc. The application is an online platform that can be accessed by users in the different branches.

This article will provide you with the list of database tables together with their entities and the relationship among the tables.

Currently the system is only available in Visual Basic version but our team can convert the project into the programming language of your choice.

Database Design

tblbranch (branch_id, location, address, contact, user_id)

Description: tblbranch database table will store information of the different branches of the company such as the address and contact of the branch. The list of branches will be encoded by the user.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblbranch` (
`branch_id` int(11) NOT NULL AUTO_INCREMENT,
`location` varchar(100) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`branch_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcategory (category_id, category_name, description, user_id)

Description: items are grouped into several categories, categories of the items are being encoded in the system and it is stored in the tblcategory database table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcategory` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`category_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbldepartment (department_id, department, description, user_id)

Description: every offices and department of the company such as the accounting and hr are also encoded in the system by the user; tbldepartment is the table that stores those information.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbldepartment` (
`department_id` int(11) NOT NULL AUTO_INCREMENT,
`department` varchar(15) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`department_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblemployee (employee_id, name, contact, depart­­­­­­ment_id, branch_id, user_id)

Description: tblemployee database tables consists of the primary key which is the employee_id, personal information of the employee such as the name, contact, department and the branch where the employee is assigned.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblemployee` (
`employee_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`contact` varchar(11) NOT NULL,
`department_id` int(11) NOT NULL,
`branch_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`employee_id`),
KEY `department_id` (`department_id`,`branch_id`),
KEY `user_id` (`user_id`),
KEY `branch_id` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblitem (item_id, item_tag, item_name, description,  serial_no, cost, date_purchased, qty, category_id, user_id)

Description: list of items in the inventory of the company should be encoded or imported in the system; the table that stores the information of items such as the item name, serial number and the quantity of items will be stored in the tblitem database table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblitem` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_tag` varchar(30) NOT NULL,
`item_name` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
`serial_no` varchar(30) NOT NULL,
`cost` float NOT NULL,
`date_purchased` date NOT NULL,
`qty` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`item_id`),
KEY `category_id` (`category_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblitememployeeassignment (assignment_id, item_id, employee_id, branch_id, qty, condition, remarks, date_assigned, user_id)

Description: the purpose of this table is to store information of employee and the items he/she is accountable for. This table is also the part of the system where monitoring of items are performed. The table includes the item_id (foreign key and the primary key of tblitem table), employee_id (foreign key and the primary key of tblemployee table) and the condition/remarks of the items.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblitememployeeassignment` (
`assignment_id` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(11) NOT NULL,
`employee_id` int(11) NOT NULL,
`branch_id` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`condition` varchar(30) NOT NULL,
`remarks` varchar(100) NOT NULL,
`date_assigned` date NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`assignment_id`),
KEY `item_id` (`item_id`,`employee_id`,`branch_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblitemimage (image_id, image_name, description, item_id)

Description: the user is allowed to upload multiple images of the items, these images are stored in the tblitemimage

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblitemimage` (
`image_id` int(11) NOT NULL AUTO_INCREMENT,
`image_name` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
`item_id` int(11) NOT NULL,
PRIMARY KEY (`image_id`),
KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbltransferhistory (history_id, transferred_from, transferred_to, item_id, qty, date_transfer, remarks, user_id)

Description: the custody of the items can be transferred from one employee to another employee; the transfer of item(s) is being recorded and stored in the tbltransferhistory. Records in the tblitememployeeassignment will also be updated if a transfer of item will happen.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbltransferhistory` (
`history_id` int(11) NOT NULL AUTO_INCREMENT,
`transferred_from` varchar(100) NOT NULL,
`transferred_to` varchar(100) NOT NULL,
`item_id` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`date_transfer` date NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`history_id`),
KEY `item_id` (`item_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (user_id, name, contact, username, password)

Description: the users of the system will be the IT personnel which will be in-charge of the encoding, updating and reporting processes.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`contact` varchar(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

—
— Constraints for table `tblbranch`
—
ALTER TABLE `tblbranch`
ADD CONSTRAINT `tblbranch_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcategory`
—
ALTER TABLE `tblcategory`
ADD CONSTRAINT `tblcategory_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblcategory_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tblitem` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbldepartment`
—
ALTER TABLE `tbldepartment`
ADD CONSTRAINT `tbldepartment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblemployee`
—
ALTER TABLE `tblemployee`
ADD CONSTRAINT `tblemployee_ibfk_3` FOREIGN KEY (`department_id`) REFERENCES `tbldepartment` (`department_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblemployee_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblemployee_ibfk_2` FOREIGN KEY (`branch_id`) REFERENCES `tblbranch` (`branch_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblitem`
—
ALTER TABLE `tblitem`
ADD CONSTRAINT `tblitem_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblitememployeeassignment`
—
ALTER TABLE `tblitememployeeassignment`
ADD CONSTRAINT `tblitememployeeassignment_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `tblitem` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblitememployeeassignment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblitemimage`
—
ALTER TABLE `tblitemimage`
ADD CONSTRAINT `tblitemimage_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `tblitem` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbltransferhistory`
—
ALTER TABLE `tbltransferhistory`
ADD CONSTRAINT `tbltransferhistory_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `tblitem` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbltransferhistory_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Features

1. Item assignment module
2. Transfer Accountability to other person
3. Print inventory by department
4. Print by location
5. Print inventory by employee
6. Inventory of items on hand/stock

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.