Payroll rates change

[blackduck]

I am after a bit of help with how to develop a database with expected change in $ Rate mid way through.

Basically the budget and course/staff/duty, goes over a semester - approx six month period.
These are the tables and attributes I have done so far:

COURSE (tbl)
STAFF (tbl)

DUTY (tbl)
DutyCode (pk)
Rate
Date ?

APPOINTMENT (tbl)
CourseCode (pk) (fk)
StaffId (pk) (fk)
DutyCode (pk) (fk)
BudgetHours

TIMESHEET (tbl)
CourseCode (pk) (fk)
StaffId (pk) (fk)
DutyCode (pk) (fk)
WeekEndDate
Hours

Any comments appreciated. Thanks

 

[alvechurchdata]

Is the problem that you need to have different $ rate depending on the date that something happens? If that's the case then it's very similar to the classic invoicing problem where last year's invoices must not update themselves with this year's prices whenever you look at them. The simple answer there is to copy the current price from the stock table into the invoice itself. Can you do the same here and copy the current rate into the timesheet or have I misunderstood the problem?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[blackduck]

Aah! Thanks Geoff, that's what I was after, and have adjusted my tables to suit.

COURSE (tbl), STAFF (tbl)

DUTY (tbl)
DutyCode (pk)
Rate

APPOINTMENT (tbl)
CourseCode (pk) (fk)
StaffId (pk) (fk)
DutyCode (pk) (fk)
BudgetHours

TIMESHEET (tbl)
CourseCode (pk) (fk)
StaffId (pk) (fk)
DutyCode (pk) (fk)
WeekEndDate (pk)
Hours
RatePaid

 

[blackduck]

Also, to identify different periods and compare the budget would it work if I had another table but didn't actually link it to anything - havent done that before and seems bad design?

FACULTY (tbl)
Period (pk) valid data in as 201001, 201002, 201101 etc
Budget$

And in the Appointment (tbl) add another pk Period, which will also have to go down to the Timesheet (tbl) as pk Period making a large composite key.