To be (a field) or not!

[lespaul]

Hello again! I'm looking again for opinions on my database design.

In the HR portion, employees accrue leave on a monthly basis. On the first of each month all employees accrue a certain number of hours depending on how long they have been a state employee. Now, an employee can have been an employee of the state for 10 years, but only worked at our courthouse for 1 year. In that situation, there's a field in the employee file, adjusted hire date, that stores when the person actually started working for the state (since the accruals are based on # of years of service with the state). Currently in our crappy database, there are fields that store each person's accrual. For instance, I currently get 10 hours of annual leave each month. When I reach the 3 year mark, that increases to 12. I'm debating on whether I should continue to store that piece of information with each person's record, or in the process that will accrue the leave, have the program calculate how long they have been here and accrue the appropriate amount of time from a look up table:

NoOfYears NoOfHours
0 10
3 12
7 14

any ideas?

les

 

[mrdenny]

A lookup table will make your life easer in the event that the number of hours ever changes.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[zemp]

I agree, use a look up table.

zemp

 

[johnherman]

Table is better as it keeps history and makes changes easier.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[lespaul]

that's kind of where I was headed too, just wanted some impartial collaberation!

thanks

les

 

[ceh4702]

Number of years of service can get complicated. Especially if there is a break in service when a person gets layed off from one position and 2 years later finds another position at another location somewhere else in the state. For this reason you need to be careful about calculating it all the time. Somewhere you need to keep track of the running count and then add to that every month. It might be nice to also have a computerized way of requesting sick days and holidays and also a timesheet system.

If you do not like my post feel free to point out your opinion or my errors.

 

[SQLSister]

Do you have any exceptions where people are given more leave than their time-in Service warrants? Positions that a difficult to fill or managers recruited from outside often get these types of additional benefits. If so your system needs to account for that.

Questions about posting. See faq183-874