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.

Options

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:

select
    person.id as PersonID, booking.id as BookingID
from
    person
        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
where
    skills_id = 1
and
    level >;= 1
and
    (
       booking.person_id is null
    or
       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.

Futures

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!

Advertisements

The Database

To recap: we have a high level design for an application that we will use to prove the RAD capabilities of Wavemaker. The app will be data driven, so it needs a data model linking skills, people, requests and bookings. We’ll keep it relatively simple at this stage, and refine. This iterative approach will help us find out how flexible Wavemaker is in supporting that approach.

People and Skills

Core to the app is the data model needed to support the business logic, or processes it automates and assists.

People

The people in the org are the key, without people there’s no org, so let’s start there. We need a list of people and to keep some information about them, identifying info like first and last name, contact info like email address and phone number although I don’t like capturing info like phone numbers here, would rather reference a corporate directory but that can be a future enhancement, let’s include the number here and deprecate it later.

Let’s provide the ability to capture a text field describing a persons home location or normal place of work, so that in future revisions we have a starting point to get clever from for optimizing expenses by understanding “nearest to”. Will probably be a separate table when we come to look at this in a future revision, but for now let’s just capture it so the manual scheduling of resources has some clues to go on.

We need to understand what skills a person has. Rather than have just a free format skills field on every persons database entry, we need something more structured that can be easily searched and maintained.

Skills

To manage skills in a structured way we n

eed to maintain a list of all the various skills in the organization. This master list of skills that will be key to our ability to match requests to people.

We’ll have an identifying name for the skill, plus somewhere to record a longer description of the skill in case similar names becoming confusing for some users.

Mapping People to Skills

Of course a person can have more than one skill, so here’s how we’re defining that relationship, by maintaining a list of skill records that individual holds. This PersonSkill record should identify the individuals entry in the main People list, identify which entry in the master skills list they hold and the level at which they are qualified in that skill, i.e. an abstract scale of depth of skill, e.g. level 1 is entry-level, level 5 is expert etc.

Skill Levels

This “level” field in the record can be interpreted and used as needed, for example you could choose a scheme that says it’s a 3 digit number, the first digit is the general level of knowledge, i.e. 1xx, 2xx, 3xx and so on up to a defined level of maximum skill.The second digit could define the hands-on capability with that skill, with 0 being none and 9 being the industry leader deep techie for example. This would mean you could have an individual with 10x level of skill is a newbie, with no hands-on skills but at least can decrypt whichever odd acronym the techy industry uses to describe the subject area. Someone with 34x could have good structured training in the subject and be very good hands-on.The third digit in such a scheme could be used to articulate an individuals ability to meet customers and communicate the value of the subject area, so e.g. 340 would mean the person in the example above should never be let near a customer, whereas 349 would mean they’re good hands-on, had some structured training and for some untraceable genetic reason is the most popular speaker on the planet in this area.

Still reading? Thanks! Let’s just leave it as you could just use the Level field as a simple 1-5 and ditch the above ramblings.

Requests for Skills

The primary business purpose of the app is to process requests for skilled individuals to be available at a certain data and time to do some work.

Requests

We need to capture when a request is made, who it is made by, what it is they want done, and when and where it’s to be delivered.

We’ll just provide a text field to put the requestors name in, and another one to put their email address in. We could create a separate table for requestors so we identify our clients and analyse them etc, but not yet, let’s crawl, then walk then run. We also create a date and time field to lodge when the request was made – you can be sure at some point someone will say they’ve been waiting too long for an answer so we’ll need to know when the request came in.

To capture what needs doing we provide a 140 char field (random choice of length, same as a twitter message so make people be concise). We’ll also capture the levels of required skills needed but more on that later.

The other important fields are when the engagement starts and ends, plus the location the requestor needs the fulfilling individual to be in.

Skills Needed

A particular request may need more than one skill so we create a list of skills requested, but only allowing skills we recognize in our master list. We also capture the level of that skill that’s needed As a result we end up with the following data model.
In an ideal world we’d provide the data model to support creating and documenting an action plan for a request, which is agreed and signed off by both the requestor and the scheduler. Maybe we’ll add this in at a later revision.

Bookings

The end result created by matching people and skills to requests is a booking. Each booking references the original request and the person booked, along with a description of what is expected to be delivered as a result of this booking.

The deliverable isn’t derived automatically. The scheduler will create a booking record by first searching for suitably skilled people who have availability at the required time. Once the individual has been selected, consulting with the individual and the requestor the booking is created with the exact agreed deliverable documented within, along with the dates and times agreed. These may vary from the original request, as often negotiation and consultation results in changes.