Document Tracking System Database Design Tutorial

Document Tracking System Database Design Tutorial

About the System

Today’s rapid development in technology prompts business organizations to invest in computerized systems for fast and efficient business operations. Technology has made different business operations fast, easy and convenient to perform and accomplish.

The capstone project, “Document Tracking System” is purposely designed for companies and organizations that allow them to electronically store and track documents. The system will track the in/out of the documents across different departments. The typical way of tracking documents is done using the manual approach. The staff will call or personally ask for updates about the documents which are time-consuming and inefficient.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_file_category – this table store the information of the file 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 category
• description – the 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(30) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_file_management – this table store information of the files in the system.

• 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).
• file_name – the name of the file
• category_id – this is a foreign key that points out to the category of the file.
• description – additional information about the file.
• tags – tags of the file
• file_upload – this will allow users to upload files
• file_type – the type of the file uploaded
• uploaded_by – the user who uploaded the file
• date_uploaded – the date the file was uploaded

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,
`file_name` varchar(30) NOT NULL,
`category_id` int(11) NOT NULL,
`description` varchar(100) NOT NULL,
`tags` text NOT NULL,
`file_upload` text NOT NULL,
`file_type` int(1) NOT NULL,
`uploaded_by` int(11) NOT NULL,
`date_uploaded` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_commit – this table store the information of commit made to the files.

• commit_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).
• upload_file – this will allow user to upload file
• file_id – this is a foreign key that points out to the file
• commit_remarks – additional information about the commit made to the file
• committed_by (user_id) – foreign key that points out to the user that commit changes to the file
• date – the date the file commit was made

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

CREATE TABLE `tbl_commit` (
`commit_id` int(11) NOT NULL,
`upload_file` text NOT NULL,
`file_id` int(11) NOT NULL,
`commit_remarks` varchar(100) NOT NULL,
`commited_by` int(11) NOT NULL,
`date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table store the information of the users of 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).
• full_name – full name of the user
• username -preferred username of the user
• password – preferred password of the user
• designation – the designation of the user
• code_name – the unique name of the user

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,
`full_name` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`designation` varchar(30) NOT NULL,
`code_name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_office_setting – this hold the information of the office setting.

• setting_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).
• office_code – this is a unique code of the office
• office_name – the name of the office
• head_of_office- the name of the head of the office
• contact – contact details of the office
• email_address – the email address of the office
• updated_by – the user account who updates the information

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

CREATE TABLE `tbl_office_setting` (
`setting_id` int(11) NOT NULL,
`office_code` varchar(15) NOT NULL,
`office_name` varchar(100) NOT NULL,
`head_of_office` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`updated_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user_log – this will store information of the user logs in the system.

• log_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).
• user_id – this is a foreign key that points out to the user
• log_type – the type of log(add, download, commit)
• file_path – the path where the file is stored
• commit_time – the time changes were committed
• commit_date- the date were the changes were committed.

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

CREATE TABLE `tbl_user_log` (
`log_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`log_type` int(1) NOT NULL,
`file_path` text NOT NULL,
`commit_time` time NOT NULL,
`commit_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_backup – this table store the backup details of the system.

• backup_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).
• backup_name – the backup name
• backup_date – the date when is the backup created
• user_id – process backup

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

CREATE TABLE `tbl_backup` (
`backup_id` int(11) NOT NULL,
`backup_name` varchar(30) NOT NULL,
`backup_date` datetime NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Summary

The following are the steps in database planning:

The first step in designing a database is to identify the business requirements. This involves understanding the types of data that will be stored and the operations that will be performed on that data.

The next step is to design the database schema. This involves creating a model of the data that shows how it will be organized. The schema should be based on the business requirements and should be as flexible as possible.

The next step is to create the database tables. These are the structures that will hold the data. The tables should be designed in a way that allows easy retrieval of information.

Finally, the database transactions must be planned. This involves deciding how updates to the data will be handled and ensuring that the database is always consistent.

All of these steps must be taken in order to create a successful database system. If done correctly, the system will be able to handle the demands of the business while preserving data integrity.

Please watch the video tutorial on how to prepare and create the different tables of the database of Document Tracking System.

Video Tutorial

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:

Document Tracking System Free Download

Online School Documents Processing System Free Database Design Tutorial

File Management System Free Template in Bootstrap and PHP

Asset Management System Free Download Template in Bootstrap

Invoice Management System Free Bootstrap Template Source code