We are building a database for Cutler’s Contracting. Austin is the business expert because it’s a company he created four years ago. Currently, the biggest issues with the company are missed and forgotten appointments, leading to upset customers, and inaccurate charging of customer, generally too little, which results in a loss of profit. There is currently no information system in place. The manual process used for record keeping consists of inputting work times into a phone calendar after being scheduled via phone calls. There is no record keeping of any sort. The proposed system will create separate tables for keeping track of customers, employees, and jobs. When a job request comes in, the information will be used to create a project in the job table, which will be linked to the customer who requested the job from the customer table. This will allow for easy tracking of hours and revenue for each job as well as having easily accessible information to be used for jobs such as employee skill sets and customer locations.
Since the company does not currently have any sort of record keeping or database, creating one will greatly increase the value of the company by allowing structure within job acceptance and increasing the quantity of jobs that can be accepted at once. The database will also facilitate matching employees with the skills required by projects, increasing job efficiency and customer satisfaction. Materials required for jobs and the hours taken to complete jobs will be kept track of, when they weren’t in the past, so the proper costs will be related to materials and the proper hours recorded for work, where money has been lost in the past because of improper tracking influenced by human error.
Each customer the company works for will be stored as well as all the employees within the company. When a customer has a project that needs to be done, an employee that satisfies the required skills will be matched with the customer to create a current job. The job will be kept track of in the job table along with the customer being serviced. The database will also store completed jobs after they are finished, for record keeping.
The employee table will include first and last names of each employee. There will be an identification number attached to each employee which will serve as the primary key. There will also be ratings of each employee for each basic job category, to effectively match employees with jobs. Each employee will have a field with the hours they’ve worked for the company. Each time they complete a job, the hours they worked will be added to their total.
The customers table will have the first and last names of each customer. There will be an identification number attached to each customer which will serve as the primary key. There will also be a checkbox for each customer which indicates whether they require regular work and maintenance; regular customers will receive a discounted rate. Each customer will have their home address in the table for convenience and the amount of hours the company has worked for them.
The jobs table will have an identification number for each job that will serve as the primary key. Each job will be given a category, corresponding to the employee table, used as a lookup wizard. The date and time that the job is scheduled to be performed will be shown alongside the amount of hours the job has been performed. The customer the job is for will also be a field in that table as well as the employee leading the job. There will be a checkbox for whether the job is complete or not. Once the job is complete, the box is checked and the job is hidden from the table as a form of record keeping.
Examples of different reports that can be generated by our database are employee job rankings, current jobs, monthly employee revenue, monthly customer activity, and average job revenue. The employee job rankings report will sort employees by their ratings in a given category from highest to lowest. This will make it easier to match jobs with employees that are best suited to perform them. The current jobs report will show all jobs that are currently scheduled to be completed in the future. It will be listed in order from jobs that need to be accomplished first to last to keep track of what is coming up and avoid any missed projects. The monthly employee revenue report will show how much each employee makes on average each month. It will also be able to be sorted by a single month or a select number of months, to allow for an analysis of the employee’s activities. The monthly customer activity report will show how much each customer has been worked for each month. This will allow the company to track the value of each customer as well as being able to make predictions for activity in future years. The average job revenue report will show the average revenue obtained from each job category each month. Example job categories are technology, gardening, construction, catering, and home maintenance.
Return to Main Website