Often it is sensible to keep things simple but, once in a while, it makes sense to take time to pursue a more challenging route. Tonight, I think, has been a case in point.
I’ve been trying to develop how we pick out a core song list for each term at church. Since recent suggestions from the assembled worship leaders didn’t have much crossover and the whole point is to create a point of focus so that the congregation has a chance of learning some of the songs, I decided to start work on an idea I have been mulling for a while: using a more complex algorithm to also feed back data from what we have actually sung recently, which means I don’t have to ask the other worship leaders for more than a few songs they would like to give a boost to.
Cue much toiling at the computer, wrestling with gnarly SQL, but I think I’ve just about done it. I ended up using a series of temporary tables to gradually create a weighted score that brings out a suitable list. This still left some fairly complex queries but, working step by step, it is much easier to keep a check on whether the results are in line with what I expect them to be. SQL makes it very easy to get the answer to the question you asked rather than the question you wanted to ask! Now to see how the new list flies in actual usage.