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