No of tables

[Robin Smith]

Hi,

I am working on an Alpha DB for a payroll service company. I envisioned tables for each state with the rates and one for the federal tax rates. Additionally, I thought about making tables for each payroll client, one for the employer (with their appropriate info), one for the employees ( and appropriate info) and one for the payroll transactions.

Since this payroll processor has approximately 200 payroll clients, this would entail creating around 600 tables. Is this feasible? I can import the data in but would this make an unwieldy database?

Any suggestions would be appreciated.

Thanks for your help.

Robin Smith

[Tom Cone Jr]

RE: No of tables

While I understand the need for separate tables for each governmental entity that imposes a tax (including cities), I wonder if all those other similarly structured tables would also be necessary?

From the perspective of the payroll services company aren't the payroll client and employer the same entities? If so perhaps a single table, with one record per employer could be used instead of two.

I would think a single table for employees would suffice. You could distinguish each using an identifier composed of an employee number and an employer number. That way any employee could work for two employers, and you could still process separate paychecks for them.

I'm not sure what data will be tracked in the payroll transactions table, but again I'd think it likely that a single table would do.

-- tom

[Robin Smith]

RE: No of tables

Thank you, Tom. Once again you have come to my rescue.

Robin :)

[Steve Dentler]

RE: No of tables

If you can get away with three or even four files per payroll client...considering the myriad state and local taxes, the federal rates, section 125 plans, 401K plans, the EIC, earning codes, deduction codes, and the fact that a day barely passes that 3,000 new pages of changes aren't published, I will personally submit your name for a Nobel prize in Creative Coding.

Notwithstanding the above, the only problem I can see in not using separate files and perhaps even separate folders, is that payroll transactions tend to grow rapidly and with 600 or more clients, you could bounce up against the 2 gig limit even before you got your first account in Guam.

[Melvin Davidson]

RE: No of tables

Just to add my 2 cents, which doesn't cover taxes :), you don't need 50 state tax tables, just one.

Just have fields for state code, zip_code, tax
and index by state & zip and/or just zip
eg:
State_Code C 2 0
zip_code C 10 0
tax_rate N 6 4