Re: Looking for a "Payroll" database schema
Date: Fri, 4 Sep 2009 17:06:40 -0700 (PDT)
Message-ID: <b1ca4aae-ed73-466c-8f81-904ad337f5a4_at_v36g2000yqv.googlegroups.com>
On Sep 4, 7:29 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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.
That is true, and my big dilemma that I debating myself is between these two solutions:
(1) I design a superset of all the grampa's tables. It would be conceptually similar to the generalized Theory of Relativity (I am exaggerating to make my point).
(2) I faithfully replicate each company client schema, forgetting about generalization. Each database is custom work. This would be much more work (the access programs would have to be customized as well). The issue of scalability becomes relevant if/when (hopefully!) the number of company clients becomes large.
> Then when you ask them to provide you data, > they use some dumb-ass interface that generates > an Excel-compatible output, different each time.
Actually, my experience is that the companies send you an ASCII or EBCDIC file with fixed length records. In my field they rarely send Excel, as the data is massive (lots of it is filler blank space).
> Your naive homework solution will probably be better > than anything you can buy, except for all the details > which would take hundreds of man-years.
Solution (2) above is beginning to look more and more attractive, at least to get started. When the client asks: "What format should we use?" my answer would be: "Whatever you have (like those "come as you are" parties). We will just mirror it in our Oracle server".
Meanwhile, I can educate myself and learn to design relational schemas.
-GP Received on Fri Sep 04 2009 - 19:06:40 CDT