What, where, when, why?

The one regular reader of this blog (yes, that’s you) will remember vividly that we’re building a data driven application which will make it easy to schedule expertise against incoming requests.

To do this, at some point we’re going to need to ask the database who is available between certain times. i.e. who has not already been booked on a gig between certain times. With the current data model that means querying the list of individuals to see if they have the right level of skills to match the skills requested and then check to see if any booking row for that individual starts or ends between the target times, and if it does, then that individual is not a hit. It took me a while to write that in English so currently I have no clue how to code it in SQL to return a list of people! I’m sure it’s possible, but it sounds complex (it appears to traverse every table and relationship in the model) and we should only do complexity when necessary, right? Figuring that it is possible is one thing, getting someone to understand it, support it and maintain afterwards is painful.


So what’s the first thing we do? Trawl the net looking for pointers, this is a problem that has been solved before, every email calendaring software deals with this, and I have no desire to reinvent wheels, just make this app a useful piece of Wavemaker. RFC 5545 Internet Calendaring looked like a good place to start but it defines how to communicate free/busy info, not a good way for arranging data in a model such that free and busy can easily be derived or stored.

Reading through the search results, there’s nothing really popping up which says “here’s a way to do this”. What I can see is that free/busy info in many cases is generated and is treated as a cache – meaning it can be recreated. This triggers some thinking about the model. If we track bookings, i.e. a period when a person is busy, can we assume that any non-booked time is free? We could do, and say to folk create a booking for things like time off on holiday, weekends etc, or, as I was thinking, we ask people to create free time records for the coming month or two, so they effectively publish their availability for gigs. One working principal I had in my head from earlier debate was that everything an individual does is a “work package” and therefore there should be a booking for it.

This would mean for internal projects, the nominated PM would create a request for a piece of work the same way as any outside requestor – this consistency appeals to me. Which means, free time is therefore defined as any time for which there isn’t a booking. Tackling that query complexity is starting to sound like the right thing to do!

Another thought which supports solving the difficult query needs is that what happens when a booking is cancelled, and that person then becomes free again at that time? In the case where we had free time records we’d need to figure out how to coalesce adjacent free time records into one record. Let’s avoid any thoughts about batch housekeeping sorting these things out, let’s avoid the problem if we can design it out.

The Query

We’ve eliminated redesigning the data model so let’s go figure out how to solve that query. The question we need to solve is who (person.id) has skill x at level y (assume we have the id for the skill, so personskill.skill_id and personskill.level >;= y, and has no booking record starting or finishing between request.startdatetime and request.enddatetime.

Let’s just say, with all the joins between tables needed to access the data to answer that question that a fair amount of time has been spent reading stuff and trying to code it up. Here’s what I came up with:

    person.id as PersonID, booking.id as BookingID
        inner join
    personskill ON person.id = personskill.person_id
        left join
    booking ON person.id = booking.person_id
        left join
    request ON booking.request_id = request.id
    skills_id = 1
    level >;= 1
       booking.person_id is null
       not (request.startdate <;= '2012-5-23 16:00' and request.enddate >;= '2012-5-23 14:00')

So once we have the MySQL (i.e. almost normal SQL) required to support answering the question we then have to convert this to HQL as that’s all Wavemaker understands. Why didn’t I try to develop the query in HQL in the first place? The Wavemaker IDE isn’t as functional as a dedicated tool like MySQL Workbench when it comes to iterative development – for this very reason Wavemaker best practise is to use database specific tools to develop the schema then import in to the project.

The Application Interface

Before we convert the query, let’s examine how it’s going to be used and where those parameters it needs come from. We’re dedicating a page within the application which presents a list of Requests, and once one is selected, a button labelled “suggest candidates” is enabled. The user presses this button and a table with the list of suitable candidates is presented. From there the user can select one user, which enables a button labelled “book person”, that creates a Booking record for that Person against the Request.

The page is accessed from the Request menu, from a menu item labelled Book Person, i.e. the flow is Request ->; Book Person.


The thinking races ahead while researching this, we wouldn’t really want to try and replicate peoples calendars, but wouldn’t it be good to be able to send out a calendar invite email for a booking so people can see it in their personal calendars too? Would save whoever is administering the system some time too, as without this feature they would have to manually send the calendar reservation. So we need an email capability within the app, and to know how to structure an invite email. More fun lies ahead!