A database architecture and algorithm to support querying for availability over a week

Question

I have an iPhone app where a user can set his availability for different times of the day, for each day of the week. Having just three availability periods per day (morning/afternoon/evening) will work for the time being, but I'd want to implement times in the future. A user will try to search for other users based on the availability. What is a good database architecture and search algorithm to implement this? I use stackmob as my backend. If some body can give me some hints or point me to an algorithm that will be able to search for an availability match, I'd really appreciate it. I have a crude way to do it currently - 21 variables (boolean each for 7 days * 3 time periods). I want to find a smarter way.

Answer 1

I would do the following:

• Store availability using date/times in the database. One record for each slot they are available. It is arbitrary what time you use to represent each slot. E.g. maybe morning is 9am, afternoon is 1pm and evening is 5pm. But by using date/time you are setting yourself up to change this to something more precise later (e.g. a start-time, end-time).
• Query each users availability within the required time frame (e.g. between now and 1 week time). Lets calling the resulting arrays A & B.

• Now run the following algorithm...

  NSUInteger a = 0;
  NSUInteger b = 0;
  BOOL foundMatch = NO;
  while(a < A.count && b < B.count) {
      NSComparisonResult result = [A[a] compare:B[b]];
      if(result == NSOrderedSame) {
          foundMatch = YES;
          break;
      } else if(result == NSOrderedDescending) { //a>b
          b++;
      } else {
          a++;
      }
  }