my table design

[1jet]

Most of you's should be familiar with my little project by now.
If not, I'm doing a little timesheet system for a friend's architecture company.
I started off with designing the forms, then quickly realised VBA is much more flexible than macros from everyone's help here.

BUT, now I've realised I made another boo boo.

This is to do with my relations.
Can you find anything that could go wrong later?

By f22a at 2008-09-25

I realised that I will have a problem if an employees pay rate goes up down the track. I will have queries and reports based on the information from tblHours.

If I add another pay_rate field in tblHours would this fix the problem?

 

[The_Doc_Man]

Usually, what you do is have an intermediate table showing the employee's promotion and pay history, with the employee ID, salary, date it went into effect. For convenience, even though theoretically not needed, you could add one more field, date it went out of effect, to make use of the BETWEEN ... AND ... syntax. Then an employee's salary on any given date is known having the ID and the date.

The idea here is a subtle point of normalization. If you kept the employee's salary in the Employee table AND that value is subject to change, you now have a field in that table that doesn't depend solely on the prime key. It now also depends on a date, but few or no other fields in the Employee table have that same dependency. The solution is to move the fields with incorrect dependencies to another table. You can group them according to similar dependencies. Say, for example, that at least some of the employee's salary changes were associated with a formal job rank. Even if it doesn't change for every raise, the fact that job rank ALSO depends on employee ID and date means that both salary and rank have the same dependency. They would therefore belong together.

If you have not read up on normalization yet, start reading yesterday. Payroll systems are treacherous and complex. If you don't normalize, you screw over someone's pay records. This is guaranteed to tick them off.

 

[neileg]

For data like pay rates I would tend to store the current rate against the transaction. This is not in accordance with normalisation principles, but it works well and is simple.

 

[1jet]

cheers all
ive made a new field on tblHours called Payment
its a calculated field (hours worked * hourly rate)