Suggestions for database design for office staff movements

1

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:

In office
Working from home
Off sick
Holiday
Visiting client
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.

2

I'd really recommend you do a (purely) logical design first, but since everyone wants to go straight to physical table design, I'll do that. But, the current structure should be irrelevant to the new design. There is no reason to crimp or corrupt the new design because of the existing ad-hoc structure.

You'll certain need more than 1 table: how much you ultimately want to normalize is up to you, but some basic normalization is required for any type of reasonable design.

Maybe this will get you started:

Staff_Members (
    staff_member_id int NOT NULL
    first_name ...
)

Staff_Out_Of_Office
(
    day date not null
    start_time time null --can be null only because events sometimes don't have a fixed start_time
    end_time time null --can be null only because events sometimes don't have a fixed end_time
    staff_member_id int NOT NULL --FK
    approved_by_staff_member_id int NULL --optional, may/may not be needed in your case
    activity_code smallint not null --encode this to insure consistency in descriptions
)

Activity_Codes 
(
    activity_code smallint not null --
    description varchar(100) --'sick', 'holiday', 'vacation', 'working from home', ...
)
3

Thanks Scott. Agree with all your points, my thought was to first try to get my head around what might be sensible to include; what other people / organisations already include / do; and what I can "easily" :slight_smile: knock up without too much trouble. My plan was to then take that into a design phase.

I'd got to the point of considering the existing, very constrained fixed-format Excel spreadsheet (but made flexible in the sense that "you can type anything you like into it") and then got myself into pondering how I would handle converting a list of "Staff ID, Date and Times" into a Calendar Grid style display of Who's In and Who's Out - and decided that querying an Events List into something that would display neatly in one-row-per-staff and one-column-per-day was a bit tricky!

The one-row-per-staff and one-column-per-day, with some colour code, does make it very easy to see at-a-glance who's in and who's out, so I quite like the idea of aiming for that.

I can see the thing growing to add "Request Leave", "Book meeting room" and "File expenses" and so on (currently handled by various forms of String & Gum) - which is fine, I'm only considering that in the back of my mind.

We already have Staff Table, Logins and a variety of other things that may be useful, so this will be a clip-on to the existing Time Recording / Billing, CRM, Matter Records Viewing etc. system.

4

If you want to add to it later, all the more important that it be normalized to start with.

The desired output format should not affect the database design. The output is always evolving, but the underlying basic structure won't change all that much once properly designed.

5

I think it
a) potentially effects the cost of building the solution
b) potentially limits the flexibility
c) is the most important thing to the user

thus I was considering both what the users want (in terms of what data will need to be stored) and how the output will look (in terms of how complex that would be to program) and then I was planning to decide whether to design based on Events (Date + Time) that a user can enter, or whether they will just have a Date (Mon - Fri) into which they can put a free-form comment ("In AM, Out PM"). I think the later is very weak (in terms of being able to leverage the database / APP for other purposes later on), but it would be much quicker / cheaper to build as less complexity and easy to display. In effect it would be very similar to the Excel grid that they have at present.

I tend to favour more complex solutions, because experience tells me that Managers will ask for that once they see the basic solution ... so might as well build it on Day One - but most people around me design & build the bare-bones first, and then tear it all down and throw it away to build Version 2 - which I think it more expensive, in the long run ...

So I was wanting to consider what other "features" are in use elsewhere / people can think of, so that I could decide whether to include them from Day One. Maybe its a daft / unusual starting point though?

On balance, now, I'm tending to think that the simple is enough - one row per Staff per Day with just an "I am in" or "I am visiting XXX" would do, together with [activity_code] as you suggested so that I can use that to influence the presentation (e.g. different background colour for in-office / holiday / visiting-client etc.)