I'm creating a checklist system that my department will be using to track who is monitoring what. I wanted to make sure this was scalable so I broke the tables down as much as I could, like so:

- ChecklistKey
- ChecklistDate
- HourKey
- TaskKey
- EmployeeKey

- EmployeeKey
- EmployeeFirstName
- EmployeeLastName
- EmployeeInitials

- HourKey
- PerHour

- TaskKey
- TaskName
- TaskDescription

The form I wanted to create would have the hours listed along a Y axis, and the Tasks listed along the X axis. If those two tables were updated, it would be reflectected by the form. My goal was to make this checklist easy to change but once I started working on the form I realized I had over-complicated it, and it woudl require some hardcore vbmacros to generate the form and interface the tables appropriately. I also put in some sample data in Checklists, and there was going to be a lot of repetition, like so:

ChecklistKey - ChecklistDate - HourKey - TaskKey - EmployeeKey
1 - 4/3/2006 - 8 - 2 - 1
2 - 4/3/2006 - 9 - 2 - 1
3 - 4/3/2006 - 10 - 2 - 1
4 - 4/3/2006 - 11 - 2 - 1

And so on. I calculated that in 1 year, this table would end up with over 65,000 rows - or 260,000 entries.

So I scrapped that, and decided that a simple linear table was the way to go. Since there is only supposed to be 1 initialed task per hour per day, I could have it layed out like so:

DateKey - Task1_hour1 - Task1_hour2 - Task1_hour3 ... Task6_hour1 - Task6_hour2, etc.

If I had 7 tasks, initialed per hour, this table would be 168 columns, or over 60,000 entries per year. Okay so getting better.

Before I started working on this 2nd design, I'm trying to think of better options. One idea I had, was instead of having each person initial per hour per task, they could put in time ranges. So my Checklists table would consist of the ChecklistKey, DateKey, EmployeeKey, TaskKey, ShiftStart, ShiftEnd, LunchBreak, LunchCover (whoever took care of that hour). If the Tasks were kept in a separate table, then that would be about 7 rows per day. If I created a more linear table and put all the tasks in Checklists, that would be 1 row per day of 42 columns. This last idea would make form creation a lot simpler.

So what do you think? Any other ideas or suggestions?