File Management with Approval Process Database Design

File Management with Approval Process Database Design

Introduction

The manual technique of File Management with Approval Process has the disadvantage of being time-consuming and redundant, which is a significant drawback. Suppose someone needs to make a change to a file; they may be required to go through the complete approval process, even though they are the only one who requires access to the file. Moreover, if someone quits the firm or is no longer permitted to access the files, the approval process may cause someone else to be unable to access the file until the permission process has been completed. This might result in a reduction in production as well as the loss of information.

The capstone project, entitled “File Management with Approval Process,” is designed as a centralized platform to streamline file organization and management. The system will streamline the process of submission and approval of the documents, which is efficient and timely. The said project will allow the business organization to store their documents in a centralized platform to ease up and simplify the process of retrieval.

With today’s rapid technological advancements, businesses and organizations must adapt to these changes and use technology to streamline their transactions and processes. Every corporate activity relies heavily on documents. Documents are sent back and forth between departments. One of the challenges faced by business organizations is how to handle their pile of documents and files. Conventionally, business organizations stores documents in their flash drives, through email threads, or personal computer. The current method of organizing seems to be very hard and time-consuming; thus, developing an information system that is efficient and effective in organizing and managing files is highly recommended.

The automation of the File Management with Approval Process is the solution to this problem. All of the relevant approvals will be gained in a timely manner as a result of this, which will speed up the process. Besides that, it will reduce the amount of time that is spent going through the approval procedure, resulting in higher productivity and a better ability to maintain information confidentiality. At the end of the day, it will make certain that everyone who requires access to files does so without delay.

Database Tables

This article will provide you with an idea about the file management system with approval process database design.

tbl_department – this table will store the information of the department encoded in the system.

• department_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).
• department_code -unique code given to a specific department
• department_name – the name of the department

Create SQL Statement – the statement below is used to create the tbl_department, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_department` (
`department_id` int(11) NOT NULL,
`department_code` varchar(15) NOT NULL,
`department_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_file_category – the information of the file categories are store in this 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 file category name
• description – additional information about the file category

Create SQL Statement – the statement below is used to create the tbl_file_category, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_file_category` (
`category_id` int(11) NOT NULL,
`category_name` varchar(15) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_department_user – this table will store the information of the system’s department user.

• department_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).
• department_id-this is a foreign key that points out to the department
• complete_name – complete name of the department user
• designation – the designation of the department user
• profile_picture – the profile photo of the department user
• username – the desired username of the department user, combined with the password to login to the system.
• password – the desired password of the department user
• account_status – (0)active,(1)inactive

Create SQL Statement – the statement below is used to create the tbl_department_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_department_user` (
`department_user_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`complete_name` varchar(100) NOT NULL,
`designation` varchar(30) NOT NULL,
`profile_picture` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_approver_user – this table will store the information of the approver of the file management system.

• approver_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).
• complete_name – complete name of the approver
• designation – position of the approver
• profile_picture- the profile picture of the approver
• username – the desired username of the approver user
• password- this desired password of the approver, combined with the username to login to the system.
• account_status – (0)active,(1)inactive

Create SQL Statement – the statement below is used to create the tbl_approver_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_approver_user` (
`approver_id` int(11) NOT NULL,
`complete_name` varchar(100) NOT NULL,
`designation` varchar(30) NOT NULL,
`profile_picture` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_file_management – the file management details are stored in this table.

• file_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_id – this is a foreign key that points out to the category of the files
• department_id – this is a foreign key that points out to the department.
• department_user_id – this is a foreign key that points out to the department user
• control_number – the unique control number of the files
• filename – the name of the file
• description- additional information of the files
• file_size – the size of the files
• file_type- the file type
• date_uploaded- the date the file was uploaded
• download_file -this will allow the file to be downloaded
• status – (0)pending, (1)for review, (2)approved
• remarks – additional information of the files

Create SQL Statement – the statement below is used to create the tbl_file_management, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_file_management` (
`file_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`department_user_id` int(11) NOT NULL,
`control_number` varchar(15) NOT NULL,
`filename` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
`file_size` varchar(10) NOT NULL,
`file_type` varchar(5) NOT NULL,
`date_uploaded` date NOT NULL,
`download_file` int(1) NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_file_comment – this table will store the information of the file comments.

• comment_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).
• file_id – unique number given to a file comment
• comment – the comment for the file.
• comment_date – the date the comment was commented to the file.

Create SQL Statement – the statement below is used to create the tbl_file_comment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_file_comment` (
`comment_id` int(11) NOT NULL,
`file_id` int(11) NOT NULL,
`comment` varchar(100) NOT NULL,
`comment_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_setting – this table will store the information of the system settings.

• setting_id
• files_allowed – files allowed to be uploaded/file type
• file_size_max_allowed – the size of the file allowed to be uploaded
• updated_by(user_id) – this is a foreign key that points out to the user who updated the settings

Create SQL Statement – the statement below is used to create the tbl_setting, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_setting` (
`setting_id` int(11) NOT NULL,
`files_allowed` int(3) NOT NULL,
`file_size_max_allowed` float NOT NULL,
`updated_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table will store the information of the system users.

• 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).
• complete_name – complete name of the user
• avatar – the profile photo of the user
• username – the desired username of the user
• password- the desired password of the user
• contact – the contact details of the user, preferably mobile number
• email – the email address of the user
• 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,
`complete_name` varchar(100) NOT NULL,
`avatar` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Video Tutorial

Summary

A table for storing information about each file, such as its name, location, and size, should be included in a database design for a file management system, according to experts. A table for storing information about each user, such as their name and the files to which they have access, should also be included in the database design. Additional tables for storing information about each group, such as the name of the group and any files that members of that group are permitted to view, should be included in the database. Finally, a table should be included in the database to record information about each permission, such as the permission’s name and the files to which it applies.

All of these tables should be indexed in order for search operations to be done as quickly and easily as possible. A database should also be constructed in such a way that it is simple to add new files or groups of files to the system, delete old files, or change the permissions on existing files. The database should also be constructed in such a way that it can be quickly and easily backed up and recovered in the event of a disaster.

Please watch the video tutorial on how to prepare and create the different tables of the database of File Management with Approval Process System.

FREE DOWNLOAD SQL FILE

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 Topics and Articles:

File Management with Approval Process in PHP and Bootstrap Free Source code

File Management System Free Template in Bootstrap and PHP

IPO Model Conceptual Framework of File Management with Approval Process

Webinar Course Management System Free Bootstrap Template

Online Birth Certificate Processing System with SMS Notification Free Bootstrap Template