Re: Looking for a "Payroll" database schema

From: John Hurley <>
Date: Thu, 3 Sep 2009 18:15:12 -0700 (PDT)
Message-ID: <>

On Sep 3, 7:20 pm, Google Poster <> 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

What about taxes? How about different kinds of deductions for 401K's and medical plans?

This sounds suspiciously like homework to me.

Before you start on the details of a payroll you need to backup and start with things like employees companies countries etc. Identify the important entities and decide how they fit into your schema before going into low level design.

Seriously most small to medium sized companies implement a packaged solution like Great Plains and even smaller sized packages.

Big companies use packaged applications from companies like Oracle etc.

It doesn't make much sense to design something like this yourself unless it is an academic type excercise. Received on Thu Sep 03 2009 - 20:15:12 CDT

Original text of this message