Our office manager has an Excel spreadsheet for office staff movements. It has one row per staff member, and one column per day (of the week). Each cells basically says one of:
Working from home
Visiting client AM
Visiting client PM
and variations on that theme.
People can open the XLS but it causes problems with being updates at the same time it is viewed, and various staff who edit it are inconsistent, and so on. So I want to build a database table(s) to replace the spreadsheet and am scratching my head a bit ...
At the most basic I could provide the user with form fields for Monday, Tuesday ... Friday and let them just pick one of the above descriptions for each of the days, but I think I ought to do something more flexible than that. Currently descriptions tend to say "Visiting client XXX back in office at 3pm" so if the user was able to enter the time I could indicate that on the chart.
Perhaps I should let the user set up an "event" with a Start and End Date / Time - so they could do "Visiting Client XXX" from 09:00 to 15:00 and then, by default, they would be in the office for the rest of the day.
On that basis they could enter a week's holiday / leave by using the Start / End dates of a single event, rather than having to fill in the form-field box for each day of the week.
But the reporting would be more complicated than if I just give them Mon, Tue, ... Fri form-field boxes to fill in.
I could also do with "templates". For example, we have some people who don't work on Fridays, so if their week pre-filled from that template then on the odd occasion when they did come into the office on a Friday they could override that value.
I thought a Google search would find me a database design, or online-apps that wanted my business, but maybe I was suing the wrong search keywords as I didn't find much that was of any use.
If you have any suggestions, URLs or experience from building something similar I'd appreciate hearing your thoughts.