>>Consider the following situation in a employee scheduling / clock punch application:
>>
>>Some employees have a very stable schedule, say Monday through Friday 8am to 5pm with one hour for lunch. Others cases are very erratic: some days one shift while another day a different one. In some situations employees are scheduled in groups while in other cases they are scheduled individually. Some employees that have regular schedules may go on rotation for a while and back to a stable situation.
>>
>>The question is how to design the tables to store the information according to good practice. The goals are total flexibility yet at the same time economy of processing and storage. Avoiding the need to generate and store data unnecessarily.
>>
>>A decision already made is to store shift definition in only one place. For example Shift # 12 is 8:00am-5:00pm with one hour for lunch. So if an employee works that shift in a particular day only the shift ID is stored in the Employee-Date table.
>>
>>I welcome any suggestions and considerations. Thank you.
>>
>>Alex
>
>I would imagine that you should keep both hours and shift id in employee attendance table. Shift id could be used for report/historical purposes, i.e. it could be optional, but hours are clearly required because some person, as you said, may come/leave earlier, etc.

Thanks Ed,

I am starting to recognize that there are three definite stages that need separate information. Starting from the last stage:

1) Need to store times actually punched in/out; any editing made to actual punches such as inserting missed punches; the times the employee was supposed to punch in/out; and times actually paid (not necessarily the same as those punched - for example employee punches in a 7:52 but gets paid starting at 8:00). This information would need to be stored for each employee and each day. Consider this information to represent "actual times".

2) Need to store shifts assigned to employees before receiving in/out punches. At this stage the application must offer the capability of easily assigning and reassigning shifts by groups or individually. This information is published and retained to match with actual punches when received. Consider this information to represent "planned times".

3) There are groups, rules and defaults which are used to generate the initial assignments metioned in point 2 above. Consider this information to represent "planning rules" which may change from pay period to pay period. Don't know whether it is necessary to store the planning rules after the fact.

I am afraid the information described above will need separate tables. What else do you see?

Alex