Payroll

[SHETTY]

Dear Friends

I am planning to design a database for maintaing payroll information. At
present i am using database where in I am unable to maintain historic data
like details of previous months. I have payheadmaster and employee master
table. Reports and payslips are generated through querries from master
table itself. data changes will be made in master table itself

Can anyone guide me how to create a database where in I can maintain/store a
data for elapsed previous months also. Report for any given period. There
will not be major changes in the payhead and employee data month after month
, changes may be to the extent of 2%
So I think it may not be good design if a store salary releated information
for everymonth and every employee. Is there any other way where I can list
only the changes in pay head data

thanks

Ramesh

 

[Fred]

A core question is: what level of detail is going into the DB? For example:

- punch-in and punch-out times from time clocks. (e.g. Access is doing the
hours calculations)
- total hours, holidays, sick days etc.
- just pay for the pay period.

Also could yu clarify what your payheadmaster table is?

 

[SHETTY]

Payment is on monthly attendance basis,
full day or half day attendance absence can be marked
Casula leave -6, Sick Leave-6 & Paid Leave -30 will be provided
leave taken in exess of above will be dedcuted from salary (loss of pay)

Leave under each of above head will be credited to respective A/c only at
the end of calender year ie 31st December. Employee can carry forward PL up
to 180 days , Sick leave up to 15 days, Casual leave cannot be caarried
forward.

Payheadmaster table stores pay details as per appoitnment order
Structure
ID (PK), EmployeeCode (FK), Basic, Da, & other pay heads including
profession tax and other deductions

at present one LOP (loss of pay days) field is provided in above table and
no of days leave to be deducted updated in that field and pay head under
each head is worked.

There will be defferent profession tax applicable for defferecent states.
Profession tax slab is pre defined and can be called for each branch or
employee wise also

There will be 3-4 grades in basic salary and modification in salary master
will happen April month of every year

Apart from monthly salary following will be paid occationally
Paid leave encashment,Leave Travel allowance, Medical expenses
reimburshment(non taxable), statutory bonus, performacne incentives, all of
above must be added total yearly salary . Our financila year is April to
March

Hope I have provided all the required information

Please guide me

Ramesh

 

[Fred]

There are several challenges here, both on learning the key info and that
this will be a project rather than just one post/question. But here's and
idea on structure.

It sounds like payheadmaster has one record per employee or may actually be
an employee table. I'll call that the "Employee" table.

It souds like your pay and pay records are a once-per month summary.

So, one "Employee" table which has all of the "once per employee"
information. Plus a PrimaryKey "EmployeeNumber"

Next a "Pay" table which has a record for each instance of a monthly entry
for an employee. It will have an "EmployeeNumber" field (NOT a PK), and
link it to the Employee table (EmployeeNumber to EmployeeNumber). It will
have a PayDate field It will have fields for all of the data that changes
pay-period to pay-period. Any calculations where the "equation" never
changes will run at the instant that you are providing the data (via a report
etc.).

Just a start, but hopefully on a foundation of a good underlying structure.

 

[SHETTY]

Thank you sir,
I want to know how I can add data to pay table month after month
automatically, may be I have to run a append querry
The update to pay table will be based on the attendance leave status etc. I
have written a querry which work out pay for current/running month. How I
can append this data to pay table with the pay date ?
Actaullay I have created database which works fine for a single month,
reports- generating pay slips all are working fine. But I dont know how to
work on date values (say for a particular period) and updating paytable
automatically as per current month salary
Please guide

Ramesh Shetty

 

[Fred]

Dear Shetty,

In my opinion, you need to start with a description of the fundamental
organization and aspects of your real world process, and of what you want
the database to accomplish. A way to force yourself to do this properly
would be to do it without using any database implementation terminology.
I believe that you have not given that in your posts. Instead, you've
given certain secondary details about your process plus various database
implementation details which are not relevant at this stage. Other than to
write this paragraph which hopefully might be useful I'm unable to help.