Database design

[desai.rohit27]

hi. i have a list of around 200 employees (which will grow). these
employees receive payment on a daily basis. at the end of each month i
need to calculate the total amount received by each employees and
deduct taxes depending on the total. please help me with an
appropriate design. thanks in advance.

 

[Steve Sanford]

Depending on how long the records must be kept (years?), there might (will)
be tax rate changes. So you should think about storing a start and end date
for the tax rate; you could calculate the tax at any time.

Using Steve's structure, here is my modifications:

TblEmployee
EmployeeID
<employee name and contact fields>

TblEmployeePay
EmployeePayID
EmployeeID 'FK to Employee table
PayDate
PayAmount
TaxRateID 'FK to Tax table


TblTaxRates
TaxRateID
TaxStartRange 'Wage low amount
TaxEndRange 'wage high amount
TaxRate
TaxBegDate 'start date of tax rate
TaxThruDate 'thru date of tax rate


HTH

 

[Ken Sheridan]

Why reinvent the wheel? There are many payroll applications for small
businesses available which will do the job far better than anything you'll
design in Access. Google 'payroll software' and you'll find plenty to choose
from.

Ken Sheridan
Stafford, England

 

[desai.rohit27]

TblEmployee
EmployeeID
<employee name and contact fields>

TblEmployeePay
EmployeePayID
EmployeeID
PayDate
PayAmount

TblPayTax
PayTaxID
PayTaxStartRange
PayTaxEndRange
PayTax

To get the end of the month total pay for an employee, total the PayAmount
field for the employee for the given month. To get his taxes, uae that
total, find the range it falls in TblPayTax and get his PayTax from
TblPayTax.

Steve

thanks a million for your help

 

[desai.rohit27]

Depending on how long the records must be kept (years?), there might (will)
be tax rate changes. So you should think about storing a start and end date
for the tax rate; you could calculate the tax at any time.

Using Steve's structure, here is my modifications:

TblEmployee
EmployeeID
<employee name and contact fields>

TblEmployeePay
EmployeePayID
EmployeeID     'FK to Employee table
PayDate
PayAmount
TaxRateID      'FK to Tax table

TblTaxRates
TaxRateID
TaxStartRange  'Wage low amount
TaxEndRange    'wage high amount
TaxRate
TaxBegDate     'start date of tax rate
TaxThruDate    'thru date  of tax rate

HTH

thanks a million for your reply too.

 

[Tony Toews [MVP]]

Depending on how long the records must be kept (years?), there might (will)
be tax rate changes. So you should think about storing a start and end date
for the tax rate; you could calculate the tax at any time.

I'd definitely store the tax amount but I'm not at all sure about
attempting to recalculate the tax at a later date. Whatever is on an
employee's cheque can't change. Even though there was a mistake that
you may have to fix up on a subsequent step.

That said storing future tax rate changes when you know about them is
a good idea as it will remove, or lessen, the amount of work that
could be required in a tight time period.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/