Availability Evolution

paperHow much and what kind of interface is necessary to tell a computer when you can work? How do you tell your boss [in person] when you’re available? How do I make inputting a [complex] rolling weekly schedule simple?

These are some of the questions I found myself asking after last week’s meeting. Pasting up a calendar and letting employees enter “instances” of availability was my first instinct, but I have to admit that this approach would require a lot of information to be displayed at one time. It could also be considered a bit unnatural. When I asked employees at my target business to tell me when they can work, they responded with words, using a common linguistic framework.

On Tuesdays I can work from 8AM until 9PM.

On Wednesday I can work all day.

On Thursdays I can work after 12PM.

On Fridays I can work before 3PM.

Day is red. Availability type green. Start is orange and end is yellow.

Which reminded me of what Andrew said about using a narrative to enter availability. By asking employees to fill in the story of their work day, I can avoid presenting them with a complicated calendar or busy form. Instead–they can fill in the blanks to convey the same information (using natural lanauge).

story scheduling

Wireframe of language based availability input (one day)

Continue reading

Database Exploration

As this employee scheduling app will be a data-centric application, I thought it would be a good idea to start thinking about how I might organize information in the MySQL database. There will be a requirement to store information about users, availability, shifts, published schedules, punches and application settings (amongst other things). It will be important to relate information wherever possible between tables, while preventing as much duplication as possible.


Since I am creating a web-based application, I thought it would be interesting to try and locate a web-based database visualization tool. WWW SQL Designer is a neat open source project that does exactly what I’m looking for. It helped me create a clear draft of how I might setup my database tables and fields.

Availability Sketches

The first component I plan to build is the employee availability section. There employees will be able to choose the days and times which they are available to work.

Availability chart

Rather than relying on a manager to remember availability, or using a complicated form only solution–I want to help employees set availability by using a point and click interface. They will be able to easily add availability and visualize their week right away. I also want to make it easy to adjust availability by dragging start and end times, and allowing multiple availability blocks during a given day (6A to 2:30P and 4P to, 8P for example).

Availability bits

Existing Software

For the project I have decided to create a self-hosted web based application. This has a number of advantages over a native or hosted web application:

  1. Platform agnostic (accessible on Windows, Mac, Linux or mobile devices) 
  2. Available anywhere (scheduling needs can be taken care of at home or on the go without any special networking setup like VPN)
  3. Doesn’t rely on third party hosting or require a high monthly payment to service provider (great for small business setup)
  4. Relatively straightforward development and update process

As it turns out, though, there are not a great number of self-hosted web applications that do what I am trying to accomplish. The closest one I could find is Employee Scheduler, an open source PHP script that has not been updated since 2004.

The software was created by Brigham Young University to help manage student employees working at a campus library. In theory the script does a number of the things I would like to–included helping employees set their availability, add shifts and set different positions.

Employee Scheduler availability selection screen

Employee Scheduler availability selection screen

The problem is that it quite difficult to navigate or complete tasks in the script. Its not immediately obvious that in order to add a shift, for example, you have to first add an area, then a position, then create a “schedule”. Even then the schedule will only populate if an employee has availability during the whole shift, and there no indication when employees have availability. Because the Employee Scheduler has not been developed for about ten years, it is challenge to install on a typical web hosting environment. I ended up creating a virtual machine in order to try it out.

I believe that I can learn from the availability selection tool, however, and I also like the idea of colour coding for employees. The script also differentiates between hours each employee desires to work (min, max, preferred) which relates to my café case study.

When I Work day overview

When I Work day overview

For the sake of comparison, I also tried a popular hosted solution. When I Work is a web based service geared towards a wide array of business types (coffee shops, schools, emergency services etc) that pay a monthly subscription fee. This implementation is of much higher quality, offering features (like daily invested hours) and interaction that make the scheduling process much easier. Adding a shift, for example, has many fewer steps than Employee Scheduler. The manager need only select an employee, click on a day, input the start and end time.

I will differentiate my solution by providing flexible options for availability selection, a punch clock and making the system self-hosted so that the small business need not rely on a third party.

Schedule making today?

In order to get a sense of how small businesses are currently making schedules, I conducted an informal survey of acquaintances who work for companies with less than 50 employees. Among the businesses represented were an independent grocery store, a car dealership and a café. I discovered that all of these (and many other) small businesses rely on paper based scheduling.

A manager or designated employee creates the schedule 1-3 weeks in advance depending on the business, and posts it in an area visible to staff. One business uses a Microsoft Word document and changes the names and times to set shifts, then prints it. Another has a schedule neatly printed by hand for each week. The other business (the café) uses a laminated piece of paper as a dry erase board, and pens in the names and shifts on the predawn table. Employees I spoke to all expressed frustration about changing their availability, requesting time off and accessing the schedule when away from work.

Of these businesses I chose to focus on the café. It has a mix of full time, part time and casual employees that work in a public out patient centre. The business is split into to parts 1) the kiosk a small counter that serves beverages and snacks in the reception area and 2) an enclosed cafeteria area that also serves soups, sandwiches and salads during peak hours of the day.


At this business scheduling has been delegated to one of the baristas. Employee availability is not always clear, and it sometimes takes several iterations and a great deal of time to create a usable schedule. Because some shifts are less desirable than others, there can be some disagreement between staff members. I learned that while there is often a good lead time, a schedule is sometimes not available until only a few days before it starts.

Project Description


Ensuring that there is adequate staff to serve customers and complete administrative tasks can be a challenge for any organization. Currently, though, many small to medium sized businesses (like cafés or independent grocery stores) rely on paper-based schedules and timesheets that require a great deal of work to create, process and distribute to employees.

Design and technology present an opportunity to enhance the scheduling process by supplementing decision making with analytical tools (like hours budgeted vs spent this week), automating routine tasks (like repeated shifts), and easing the publication of the schedules. During this term my goal is to discover exactly what obstacles managers and employees face with schedules, and create tools that could improve the scheduling workflow.

I will deliver a functional prototype based on HTML, CSS and JavaScript (with a PHP + MySQL backend) that will demonstrate a possible solution for small business employee scheduling.