Business Permit Application and Monitoring System Database Design

Title: Business Permit Application and Monitoring System Database Design

Technical Background:

Simplifying your Business Permit Application Monitoring process may further be enhanced by incorporating advances in information and communications technology (ICT). This strategy could simply mean acquiring computers for system which the staff may use in encoding and storing data as well as in printing relevant documents. Recently, more and more LGUs are starting to realize the advantage of investing on customized computer programs designed to facilitate transactions.

The computer-based BP provides a consistent and accurate facility for recording a taxpayer’s dues and payments. It helps the LGU provide better delivery of services through fast and efficient information on billing and processing of payment of taxes and fees. It can also provide automatic computation of Mayor’s Permit fees, business taxes and other fees. Various reports such as master list of business establishments, business tax collections, collectibles and delinquency, among others, can be generated by the system as well. This feature may lead to better planning and delivery of services through accurate, timely and relevant business information.

Integration and System Testing

Expert Testing

In this phase, it was where the Business Permit Application and Monitoring System was integrated and compared with the existing manual systems of the office; and was tested by the intended respondents or users.

Upon the system has undergone testing, discussions about its flaws, if possible, will be tackled in this phase to know whether the system functions well or not.

For the evaluation of the developed record keeping system, the developers personally set an appointment with the selected software engineers for software quality assurance evaluation, using the McCall’s Software Quality Model and after the system was tabulated and evaluated.

Final Testing

After a thorough evaluation made by an expert, finally the system is ready for the user acceptance testing to double check and evaluate the features and functions of the developed Business Permit Application and Monitoring System. This is to ensure that intended functions and features are working properly and that the system is functioning according to the users’ expectations. The developers used a self-made testing tool for the final evaluation of the system and the respondents used are the same with the initial testing.

Development and Testing

The developed system will undergo different testing in order to evaluate the proposed features that are included and to be included in the system. The system will be initially installed for the intended users’ evaluation and testing using a self-made testing tool. Below are the distributions of the respondents for the initial and Final Testing.

Database Schema with description of table:

This section will provide you with the list of tables for the Business Permit Application and Monitoring System, it also includes the explanation and create SQL statement for every table.

Accounts table will store information on the users who can access the system; it includes the username, password and the privileges of what can he/she do.

Example: if the admin gives or allow the user to print then that user with that kind of permission can print the records.

tblaccounts (accountid, username, password, accounttype, canAdd, canDelete, canUpdate, canPrint, canLogin)

tblaccounts create SQL statement:

CREATE TABLE `tblaccounts` (
`accountid` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`accounttype` int(2) NOT NULL,
`canAdd` int(2) NOT NULL,
`canDelete` int(2) NOT NULL,
`canUpdate` int(2) NOT NULL,
`canPrint` int(2) NOT NULL,
`canLogin` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Company setup table will store information of the name of the city, the address, contact number and the name of the website if there is any.

tblcompanysetup (companyid, cityname, cityaddress, citycontact, citywebsite)

tblcompanysetup create SQL statement:

CREATE TABLE `tblcompanysetup` (
`companyid` int(11) NOT NULL,
`cityname` varchar(50) NOT NULL,
`cityaddress` varchar(100) NOT NULL,
`citycontact` varchar(11) NOT NULL,
`citywebsite` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Assessment table will store records and information on the different assessment and its corresponding name.

tblassessment (assessmentid, assessmentname, amount)

tblassessment create SQL statement:

CREATE TABLE `tblassessment` (
`assessmentid` int(11) NOT NULL,
`assessmentname` varchar(30) NOT NULL,
`amount` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Business assessment table will store records of the assessments for every business permits, it also includes the amount and who processed the transaction.

tblbusinessassement (businessassessmentid, permitinfoid, dateassessed, amount, processedby)

tblbusinessassessment create SQL statement:

CREATE TABLE `tblbusinessassement` (
`businessassessmentid` int(11) NOT NULL,
`permitinfoid` int(11) NOT NULL,
`assessmentid` int(11) NOT NULL,
`dateassessed` date NOT NULL,
`amount` double NOT NULL,
`processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Clearance table will store information on the different clearances for the business permit application. The information includes the name and its detailed description.

tblclearance (clearanceid, clearancename, description)

Business clearance table will store and record information of the clearances complied and not complied by the business organization.

tblclearance create SQL statement:

CREATE TABLE `tblclearance` (
`clearanceid` int(11) NOT NULL,
`clearancename` varchar(30) NOT NULL,
`description` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblbusinessclearance (businessclearanceid, clearanceid, permitinfoid, status, processedby)

tblbusinessclearance create SQL statement:

CREATE TABLE `tblbusinessclearance` (
`businessclearanceid` int(11) NOT NULL,
`clearanceid` int(11) NOT NULL,
`permitinfoid` int(11) NOT NULL,
`status` int(2) NOT NULL,
`processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Collection table will store information of the orno number, the permit information, sum of assessment, the payment amount and who processed the transaction.

tblcollection (collectionid, orno, permitinfoid, sumoftotalassessment, dateprocessed, paymentamount, processedby)

tblcollection create SQL statement:

CREATE TABLE `tblcollection` (
`collectionid` int(11) NOT NULL,
`orno` varchar(15) NOT NULL,
`permitinfoid` int(11) NOT NULL,
`sumoftotalassessment` double NOT NULL,
`dateprocessed` date NOT NULL,
`paymentamount` double NOT NULL,
`processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Collection amount table will archive the records of collection per month and year.

tblcollectionamount (monthname, amount, year)

tblcollectionamount create SQL statement:

CREATE TABLE `tblcollectionamount` (
`monthname` varchar(15) NOT NULL,
`amount` double NOT NULL,
`year` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Permit information table will store the information of the permit application of the business organization. It includes the type of permit (new or renewal), mode of payment, dti registration, tax information number, name of the business, tax payers name, and many more.

tblpermitinfo (permitinfoid, typeofpermit, amendment, modeofpayment, dtireg, dtiregistrationdate, organizationtype, tinno, taxpayersname, businessname, buildingname, businessaddress, capitalization, grosssales, expiryyear,  status, processedby)

tblpermitinfo create SQL statement:

CREATE TABLE `tblpermitinfo` (
`permitinfoid` int(11) NOT NULL,
`typeofpermit` int(2) NOT NULL,
`amendment` varchar(15) NOT NULL,
`modeofpayment` int(2) NOT NULL,
`dtireg` varchar(15) NOT NULL,
`dtiregistrationdate` date NOT NULL,
`organizationtype` varchar(15) NOT NULL,
`tinno` varchar(15) NOT NULL,
`taxpayersname` varchar(50) NOT NULL,
`businessname` varchar(50) NOT NULL,
`buildingname` varchar(50) NOT NULL,
`businessaddress` varchar(100) NOT NULL,
`capitalization` double NOT NULL,
`grosssales` double NOT NULL,
`expiryyear` date NOT NULL,
`status` int(2) NOT NULL,
`processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Permit statistics table will archive the records of permits per month and year and group it according to type (new or renewal)

tblpermitstat (month, newbusiness, renewal, year)

tblpermitstat create SQL statement:

CREATE TABLE `tblpermitstat` (
`month` varchar(15) NOT NULL,
`newbusiness` int(11) NOT NULL,
`renewal` int(11) NOT NULL,
`year` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ERD:

Business Permit ApplicatioBusiness Permit Application and Monitoring System ERDn and Monitoring System ERD

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

Hire our team to do the project.

Comments

comments