keeping a running total

[shocketi]

i need to set up a database that will track all employee's credit hours on a
per pay period basis...I also need running totals by department...the catch
is... if an employee moves to a new department, the old hours need to stay
with the old department...how do I set this up?

 

[Ken Sheridan]

Your description is a little brief so its difficult to be categorical, but in
essence it’s a question of functional dependency. The credit hours are
functionally dependent on a key which includes the combination of Employee
and Department, not just Employee. Consequently in whatever table the credit
hours are being recorded you need foreign keys referencing the keys of both
the Employees and Departments tables, e.g. EmployeeID and DepartmentID.
These will form part of the table's primary key (or at least a candidate key).

You will then be able to aggregate the credit hours by employee, by
department or by department/employee over whatever time period you wish, by
grouping on the relevant column or columns.

Another possible approach would be to record the dates when each employee
joins and quits a department. If credit hours are also recorded by date you
can then determine which department they apply to. In this case you'd join
the relevant tables where the credit hours date is between the start and end
dates to aggregate them per department or department/employee.

Ken Sheridan
Stafford, England

 

[shocketi]

I guess what they want is totals for each employee and also a total for each
department and they want the hours to stay with the department even if the
employee moves to a different department, is that a better description? I am
relatively new to the db world so this is really challenging me to say the
least. We operate on Fiscal years and Pay periods so that makes it a little
more challenging, maybe if I can have them use dates instead that would help
a little bit.

 

[Ken Sheridan]

If we assume that the pay periods relate to the fiscal year, i.e. the start
and end of the tax year is also the start and end of a pay period then you
don't only need to relate the credits to the pay period as this determines
the fiscal year, e.g. if the fiscal year runs from 1 April to 31 March say,
and the pay periods are monthly then the third pay period of 2008 is in the
fiscal year 2007/08, but the fourth pay period of 2008 is in the fiscal year
2008/09.

So credits to Employee 42 in Department 1 in January 2008 would have values
such as Department 1, 2008-1, and 42, along with the credit hours, in a row
in a table which models the relationship between the Departments, PayPeriods
and Employees tables. Its then a simple matter of joining the tables,
grouping on whatever column(s) are relevant, and aggregating the credits.
So the aggregated credits could be by employee or department and over fiscal
year or pay period, i.e. whatever combination is required for any particular
purpose.

Ken Sheridan
Stafford, England