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

On Sep 3, 7:20*pm, Google Poster <gopos... (AT) jonjay (DOT) com> wrote:

snip

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.

On 04/09/09 02:15, John Hurley wrote:
assignment for the year - or his thesis. Few people actually bother to
learn or understand their subject these days. They just want someone
else to provide a ready built solution.

On Sep 3, 9:15*pm, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:
I am sure it does, because I would like to get a barebones schema and
finish the real world details myself.

Thanks for the "homework" compliment! I graduated in the 80s.

Tutorial stuff is useful even to us oldtimers.

-Ramon

On Sep 3, 9:15*pm, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:
This is far from being a full blown up accounting package! My company
(a small startup) will provide supplementary services, downstream.
Payrool are read-only for us. All I need is a few tables (have some
already, DON'T like them), the general structure to get started.

There is only one country involved: good ole' USA.

This has got to be in a book somewhere. It is a textbook case.

-Ramon

On Sep 3, 7:20*pm, Google Poster <gopos... (AT) jonjay (DOT) com> wrote:
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 --

On Sep 3, 4:20*pm, Google Poster <gopos... (AT) jonjay (DOT) com> wrote:
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 man-
years.

jg
--
@home.com is bogus.
http://www.wikio.com/themes/Andrei+Melnikov

On Sep 4, 7:29*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
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.

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).

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