Income Database Design

[John V]

I have been asking questions (and getting solutions) to various things related to forms on the forums here. However, some of the responses have been making me wonder if I am taking the wrong approach to a few things.

I am trying to design a database for keeping track of my income. I work for several different employers and they basically pay me in three different ways (depending on the employer).

Most of my income is from unionized employers who pay through a payroll system complete with Retirement Plan contributions, Vacation Pay, and various government source deductions. I am paid by the hour by these employers.

I also work for employers who pay me based on invoicing. Some of this invoicing is based on an hourly rate and some is based on a flat rate (negotiated) per job or task. This invoiced income does not include any of the deductions of the Payroll System income. I am responsible for calculating and submitting the remittances myself.



With the Hourly, Unionized, Payroll System income there are several values that change either yearly or when the Agreements are renegotiated. These are:

• Retirement Contribution Plan Rate (%) (Agreement Based)
• Vacation Pay Rate (%) (Agreement Based)
• Pay Rate ($) (Agreement Based)
• Provincial Tax Exempt Amount ($) (Set by the Government)
• Federal Tax Exempt Amount ($) (Set by the Government)

The Retirement Plan Rate and Vacation Rate generally change once every 2 or 3 years (not necessarily based on a calendar year). The Tax Exempt Amounts change Yearly (Based on a calendar year), and The Pay Rate changes yearly (not necessarily based on a calendar year).

So far, because the rates and amounts change on different dates, I have been keeping track of date ranges using a Start Date and End Date for a particular Rate (Pay Rates for instance). I don't believe I can just keep track of the rate and not the dates because the calculated amounts for my historical data will change. I need the calculation to be done using what ever rate was "in play" on the date of the work being done.

When I get to dealing with the Tax Exempt amounts (which change yearly, based on a calendar year) I was planning to just store a year.

I am not the first person to design these types of databases, I am wondering what methods and practices are used for the issues I have mentioned above? Are there different ways of dealing with these issues other than what I am thinking?

Any thoughts and comments are welcomed.

John V

[June7]

Do you really need to document the breakdown of the hourly pay?

Seems to me you really need a db for tracking contracts and invoicing and receipts. Have you considered OTS program like QuickBooks?

[John V]

June7,

Thank you for your response. I have used Quickbooks and Quicken in the past, and I am still using Quicken now for my Banking and Finances. Neither of these programs actually do what I want this database to do.

It is important for me to track the hours because in unionized environments it is actually not as much about the rate paid as it is about how the hours are calculated (Regular Time, Overtime, Double Overtime, Rest infringement, Meal Penalties etc.). Since I generally work in unionized environments (very little of my income comes from Invoicing) it is important that I track my hours. The employers I work for are often dealing with more than one union. The Actors union has a different set of rules than the Musicians union, which has a different set of rules from the Technicians union. They are also dealing with unions for the Building Maintenance and Administrative positions.

The employers do make mistake with how the hours are calculated. I am not blaming, the payroll departments have a lot of balls in the air at any given time. It is up to me as the employee to ensure that I am being paid according to the rules of the agreement I am working under.

I apologize for rambling. I will stop, as this is actually fodder for a completely different forum.

The short of it is I need to have a method of tracking my hours (and potential income from these hours).

Besides where is the fun in using somebody else's database design. For me half the fun is in the challenge of making it work!

This forum is a fantastic learning tool and (in my opinion) the best place on the web for learning how to use Access.

Thank you June7 (and all of the moderators here) for all of the time and effort you put into this forum.

With Respect.
John V

[ItsMe]

I would engineer tables around the contracts and their types. I would then create queries to manage contracts and their addendums. The result should be a DB that functions autonomously, Contracts DB. After I had this functioning, I would create another set of tables to manage my hours. I would add PK values from the Contracts DB to my new set of tables. I would then create queries that look at ALL tables to retrieve the relative info needed.

[John V]

Thank you ItsMe.

I believe I am actually heading in that direction and have made a few changes to my design to get me closer to this.

I have a table to keep Employer information. The usual things (Employer name and contact information, Pay period information and the type of contract it is (Hourly or invoiced). I have added tables for Retirement Plans, Vacation Pay, and Tax Exemption amounts. I originally started off with Retirement Plans, Vacation Pay and Tax Exemptions all in one table but very quickly realized this would be an issue if I wanted to update the Vacation Pay information and not the Retirement and Tax Information this would be an issue.

The Employer Information does not change but the rest of the information does and they all change at different times (Dates). I have used the Employer ID (PK in Employer Table as the FK in the other tables).

I already had a separate of tables to track Events, Venues, Job Positions, Pay Rates and Work Calls.

I do not believe that I (now) all that far away from what you are suggesting.

Thank you for the guidance.
John V

[ItsMe]

OK, let us know if you have more questions. I will assume that a new thread may be in order. Following the path you described will likely create new questions not yet perceived.