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