Re: Looking for a "Payroll" database schema
From: joel garry <joel-garry_at_home.com>
Date: Fri, 4 Sep 2009 16:29:03 -0700 (PDT)
Message-ID: <00ddc06b-3b35-4569-bd32-75e7fe5f9779_at_z3g2000prd.googlegroups.com>
On Sep 3, 4: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
Date: Fri, 4 Sep 2009 16:29:03 -0700 (PDT)
Message-ID: <00ddc06b-3b35-4569-bd32-75e7fe5f9779_at_z3g2000prd.googlegroups.com>
On Sep 3, 4: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
Actually, most of those companies use stuff that has come down from Grampa's COBOL. Then when you ask them to provide you data, they use some dumb-ass interface that generates an Excel-compatible output, different each time.
The government rules are so complex and ever-changing, a lot of it is just automated manual processing.
If you buy a packaged solution, they'll generally have some sort of procedural update for ever-changing data.
Your naive homework solution will probably be better than anything you can buy, except for all the details which would take hundreds of manyears.
jg
-- _at_home.com is bogus. http://www.wikio.com/themes/Andrei+MelnikovReceived on Fri Sep 04 2009 - 18:29:03 CDT