Re: Looking for a "Payroll" database schema
Date: Fri, 4 Sep 2009 08:43:51 -0700 (PDT)
Message-ID: <393a5443-e93e-4231-be14-6b74db34bbcc_at_g19g2000yqo.googlegroups.com>
On Sep 3, 7:20 pm, Google Poster <gopos..._at_jonjay.com> wrote:
> I am sure there are versions of this out there. It would be great to
> educate myself on design of schemas. This can be a great textbook
> example.
>
> What I need is to put a set of payrolls in a dabatase. Think of the
> job performed by companies like ADP or Paychex: they have lots of
> client companies, and each client company has different payroll
> structures and periods: some pay monthly, some pay weekly, etc. It is
> important to record the number of hours worked, and the date the check
> was written (payday) as opposed to when the wages were earned. A
> realistic schema should include: regular pay, overtime pay,
> commissions, etc.
>
> I tried to design it but I simply lack the experience. All I have done
> so far are much simpler tables. For instance, if companies pay:
>
> - weekly
> - biweekly
> - semimonthly
> - monthly
> etc.
>
> Should I have different tables, one for each of the pay cycles above.
> Is it possible to structure the different cycles above in one table?
>
> I guess my neurons are not wired in a rectangular-relational shape,
> for problems like the above I immediately think of trees, graphs and
> all kinds of non-regular structures.
>
> My respects go to folks who design those complex tables.
>
> Pointers and advice are most appreciated and welcome.
>
> TIA,
>
> -GP
The fact the payroll needs to be ran weekly, bi-weekly, or twice a month makes no difference to the table structures. The frequency of the processing is just a schedule. The processing logic for each run of the payroll is the same. Only when you run the processing varies, not the logic necessary to compute the payroll itself nor the structure necessary to support the data.
A vendor selling a payroll system service offering would have several choices on how to handle multiple customers. Each table could have a company/customer_code columns a part of its key to allow segregation of the end user data or a complete set of objects could be set up in a separate schema one per customer.
A complete payroll processing system would likely have to have sections of the application devoted to calculating hourly employee information and another one for calculating salary employee information.
If a payroll service provider is used in most cases the hourly processing data is calculated on the front-end by the customer due to customer specific union contract rules and provided in a format that allows the service provider to complete the processing producing the check, list of deductions, taxes, and pay breakdown information.
HTH -- Mark D Powell -- Received on Fri Sep 04 2009 - 10:43:51 CDT