WDIM331 - Assignment - Small Business Accounting tool

This is a tool that will help freelance designers and small companies handle 3 main accounting tasks:

1. Time Tracking
2. Invoicing
3. Expense Tracking

It will allow employees to track their time on projects, so the manager can invoice their various clients with ease and accuracy.  It also allows managers to track their expenses, with the ability to provides reports at the end of the year for tax purposes.

The tool will involve programming (which you will not do), and a MySql database (which you will design, build and test). 

The assignment comes in 2 parts. The first week, get a good grasp of the issues, needs and requirements, and design the database on paper. Create an ERD diagram. Identify the primary and foreign keys, and indicate the relationships.

Next you will build the database, populate the database with sample data, and write 6-7 queries to test that the database will meet the specifications. 

Project Specs:

Registration & Login

There will be one main adminstrator – the owner of a small company, for example. He or she may have employees, and they will all need to track their time on the work they do towards various clients.

The main administrator needs to see reports of all the time entered by his employees, and needs to be able to generate the invoices.

The employees only need to be able to add/edit/delete their time entries.

Manage Employees

Here the admin will be able to add/delete Employees. See the wireframes for the expected fields.

Main Screen

On the main screen, the user (employee or manager) will see a form that allows them to enter work they have done, as well as time.

The form will have these fields:

• Select the Client (a drop menu of clients in the system)
• Enter date of work done
• Enter time spent on work – number of hours to nearest quarter hour, like: 3.75 hours, 2.5 hours, 1.25, etc..
• Select the project to put the time against, or select that the work is to be billed hourly.
• Description of work

Below the form, they will see a list of all the work records they have entered in which have yet to be invoiced. They will be able to edit or delete any of these records.

For the admin, this main page will have extra features. There will be a small reporting box with the following breakdown:

• Clients that have work to be invoiced.
• Number of hours worked for each client
• Dollar amount that each client will be invoiced.
• Total dollar amount to be invoiced (all work for all clients summed)

Here the admin can add / edit and delete clients.  For each client he will be able to enter:

• Client name
• Phone Number
• Address (city, state & zip)
• Email address
• Work rate – the per hour rate that he charges to do work for this client

Make An Invoice

Here the admin will be able to select a client from a drop menu, and see a list of all the work done for that client which has not been invoiced. Each record will have a checkbox, and he can select which records to include on an invoice. 

Once the records have been selected, he will click a button “Make the Invoice” – which will show a new screen that is formatted as an invoice. It must include:

• Administrator’s Company information (Name, address, phone)
• Administrators company logo
• Client’s information (name, address, phone)
• Date
• Invoice Number
• An hourly breakdown of the work, with each task on a line, along with the hours spent on them.
• Total hours worked
• Hourly rate
• Total amount due.
• Please make check payable to “company name”

The invoice can then be either printed and mailed, or saved to a file and emailed.

On this page, the admin can see all the invoices that have been generated. For each invoice in the list, it should show:

• date,
• invoice number, and
• client.

 He can click a box to indicate that a particular invoice has been paid, and enter the payment date. He can also click on each invoice to bring up the details of that invoice.
This invoice list needs certain filter controls:

• filtered by year
• filter by client
• filter by paid / unpaid

Expenses

The accounting tool will also have an area for entering and tracking business expenses.  For tax purposes, a business is allowed to enter their yearly expenses into 1 of several categories.  They must have a record of each expense.  It's a time-consuming task to go through all the reciepts at the end of the year adding up and categorizing expenses.

The admin should be able to enter:

• Date of the expenditure
• Amount
• Description  (ie:  dinner at Olive Garden with client)
• Select from tax category

They will need to see a list of all the expenses recorded, and be able to filter and sort them by date, and by category.  One report that will be most helpful will be a category expense report, showing the amount spent in each category for a given year.

Designing the Queries to test the DB design:

When you write a query, it's best to do it just as it's done in the book.  Think of the information you want to show first, perhaps filtered in a certain way.  Write the problem you are addressing, then write the query to answer that problem.
For example:  
Problem:  I want to see all the expenses (date, description, and amount) in the category "Dining and Entertainment" for 2010.