CODEWITHSUNDEEP
mysqldatabase-schema
Jordan
Jordan

Reputation: 3022

Payroll Tax Database Schema (United States)

So here is the basic question:

US Federal Income Taxes can change yearly, or even in mid year. They're based on Tax Tables, whether or not you're single or married, how often you get paid and how many allowances you claim. Here is an example Tax table for someone who is Single and gets paid Bi-Weekly (for 2013):

Gross income between $42 and $214  - $0.00 plus 10% of excess over $85
Gross income between $214 and $739  - $17.20 plus 15% of excess over $214
Gross income between $1479 and $3463  - $191.95 plus 25% of excess over $1479
Gross income between $3463 and $7133  - $687.95 plus 28% of excess over $3463
Gross income between $7133 and $15406  - $1715.55 plus 33% of excess over $7133
Gross income between $15406 and $15469  - $4445.64 plus 35% of excess over $15406
Gross income between $15469+             - $4467.69 plus 39.6% of excess over $15469

So the different pay schedules are Weekly, BiWeekly, Semimonthly, Monthly, Quarterly, Semiannually, Annual and Daily. There are two tax tables per schedule (Married and Single) with different figures in each.

So, to calculate a payroll you have to pull in an employees Deductions (health insurance, etc) Pay type, Pay schedule, garnishments (if any) then take the range of their gross pay and figure out where in these tax tables that figure falls then calculate taxes.

What is the best way to setup the DB so I can edit any or all of these figures as needed - but at the same time not make my SQL queries 100 lines long when trying to calculate a payroll for a group of employees? (If possible)

Create a table for each Pay Schedule? Two tables (married and single) for each Pay Schedule? At this point I think that is the way to go, but I just want to make sure I'm not over complicating. Any or all advice welcome. Thanks in advance.

Upvotes: 0

Views: 1390

Answers (1)

user170851
user170851

Reputation: 405

I have done complete payroll (Indian)primarily using MySQL stored procedures... Based on my experience

Using stored procedure it can be done (will reduce considerable code) Sum (total)Income from monthly/bi-monthly table payout.. Create a separate table for all exemptions. You can use if / case condition to calculate tax based on your requirement. Hope you have employee personal data table, you can store Married or Single status, while calculating tax you can pull from there.

If you wish will paste my code

Upvotes: 1

Related Questions