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!


It does what it says on the tin

This will be a short post, why? Wavemaker does exactly what it says it does. I was able to create a basic app, then add a more complex interface, test locally then deploy locally to a micro cloudfoundry instance, then finally into the public one.

The URL is tpa1.cloudfoundry.com, and it just works. Use the tutorials to get up to speed then the screencasts / YouTube material. The db many to many screencast 3 is the basis of the skills matrix page you see. I’ve started working more streamlined pages based on the invoice app and crm app available from the tutorials.

Gotchas? Just don’t make the foreign keys and indexes too complicated, with more than one item making up an index it appeared to confuse the widgets for the database. Also if you alter your schema and reimport you don’t have to throw away those dojo grids and related editors dragged in from the database widgets palette – simply go to the live views that underpin and you can tick the added fields and they then appear.

Loads of questions from here though, like backup and recovery of hosted databases in cloudfoundry and building audit trails into the app, then scalability. Also how to create good test harnesses?

More to follow.

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.


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.


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.


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.


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.

The Application

Wavemaker, MySQL and CloudFoundry are the first RAD recipe we’re working through. To do that we need to build an app and so we need to think about what we want that app to do – the industry standard “Hello World” application is useful for about 3 minutes, so we need something that will work through numerous features. How about this – most organisations have processes that run on data held in spreadsheets that are poorly managed, usually inconsistent, and just generally, well – full of holes! We can probably all think of a few examples. A colleague described spreadsheets as the IT equivalent of duck tape – ie where there isn’t a proper tool for the job, just use duck tape.

This duck tape application is about workstacks, allocating work to teams of individuals with different, complementary skill sets, so resources and bookings. It could easily be meeting rooms with different properties and booking requests being made for rooms with needed facilities, eg projector, or 15 seats, etc. So no rocket science here, hopefully!

Revision 1

What do we mean rev 1? Well Rome wasn’t built in a day so what minimum functionality is going into the first pass of this, and what different features is this going to involve learning on the platform.

Initially we’re looking to store lists of people and their different skills, plus the requests for those skills to be delivered, when and where. There needs to be some reporting to match the right people to a request, ie they’re not already booked at the requested times and then allow a manual booking record to be created for that individual against the request.

We’ll pull a few reports together that seem useful, eg monthly utilisation reports once we have the datamodel up and running plus the capabilities above. All of this running multi-user, in the cloud.

Revision 2

Would like to introduce the concept of managers and teams, maybe some multi-tenant capability, but need to explore if that’s the best way, given there’s some things you’d want a team lead and delegate to do, then there’s an overall scheduler role who would have the ability to pick from any team. Maybe roles is a better feature for this than multi-tenancy in the app, we’ll see.

Revision 3

Bidding for requests – setting the business process up to be like taxi jobs, i.e. the requests get posted and those with appropriate skills get notified of a potential job. They can then bid for the job, and the overall scheduler can grant the jobs applying whatever management principals and policies are in place, e.g. trying to grow the number of people with depth in a particular skill by avoiding jobs always going to the one strong individual. Similarly managers could goal the individuals in a manner which pushed them outside their comfort zone, increasing the depth of the talent pool and also developing the individual. Just examples, eventually such policies and rules could be coded up in a rules engine that makes best judgement recommendations for the right people to service a request, wouldn’t that be neat …

Locality – requests might need people to be in different places, is there a way to introduce mapping, i.e. is it crazy to offer someone up 3000 miles away from their morning booking.


That’s the initial thoughts. As with all roadmaps and forward looking statements, much may change! After revision 1, I’d like to pull some colleagues in and start to have people tell me what would useful functionality to add and in what order. OK, I’ll probably cherry-pick features that push Wavemaker out beyond my comfort zone (which is actually quite small right now!) just to hav a reason to learn more of the platforms capability.

Next stop the datamodel, or otherwise put an excuse to play with MySQL Designer or whatever other open source toolsets are out there.