Open Past… what day?

Posted on: Monday, Mar 3, 2008

It’s been too long since the last update, especially since we’ve overcome a lot of hurdles in building this system. I’ll talk about each in its own right.

Hours of Operation

The primary quandary we faced was in how to represent a store’s hours of operation in the database. The obvious implementation is to have separate columns for each day’s open and close times. This gives us 14 columns added to the ‘store’ table, named ‘monday_open’, ‘monday_close’, ‘tuesday_open’… and so on. Since we have a different column for each day, it would make sense to have the column be of type ‘time‘ right? No!

Especially because we’re dealing with locations that are open late, we need to deal with cases like “On Monday, we’re open from 10am – 2am”. If the columns only handle time, then we’ll have a store with monday_open = 10:00 and monday_close = 2:00. That means we need to do some serious condition checking to see if its open. Better yet, how do we query for all stores that are open at a specific time? Not very efficient.

So how did we deal with this? We want the columns to be able to know not just the time of day, but also the day of the week. For the example above, we want the columns to read more like monday_open = Mon 10:00 and monday_close = Tue 2:00. This would make queries a whole lot easier, as we can resort to a simple open < right_now < close test, without having to create extra logic. But SQL databases don’t have a type to represent time + day of week. The options are time (hours, minutes, seconds only) or fullblown datetime (year, month, day, hour, etc). Since we’ve concluded that time is not enough on its own, we’re forced into using a full datetime field.

But wait! That means monday_close will look more like March 4, 2008 2:00 -0800. How are you going to deal with the comparisons when it’s now June? The condition will always fail, thinking the store closed months ago! And that’s where our solution comes in. The solution is, to some degree, in the question. Our comparisons will involve the entire datetime, but we only care about day-of-week and time. Therefore, we just need to pick arbitrary values for the rest of the datetime fields and have the code enforce their usage. We ended up using the week of Jan 1, 2007 for our project, primarily because Jan 1 is a Monday, making it very easy to translate a number to day-of-week. Throw in a few helper functions to make the date translation seamless to the user, and we’re set!

Website Hosting

As I said in my previous post about web hosting companies, DreamHost is a great cheap shared-hosting solution, but its Rails support leaves a bit to be desired. I was pleased to see that they’ve cleaned up their documentation, and getting a Rails site running requires a lot fewer hacks now. But the speed issue is still a huge one. Big enough for me to still say that for a commercial Rails app, I would not use them. For a class project, they’re perfect.

Introducing the Team to Rails

We’ve transitioned the project to Rails completely, which has made, as usual, a lot of grunt work disappear into thin air. It’s working well for what we need from it, albeit a very simple site. The real issue with Rails is that, being so new, still very few people know about it, much less have used it before. Whenever introducing a group of people to a new technology, you’re going to have mixed results, and our team is no exception. Some have taken to it excitedly, some with reserve, and some are just not very interested. All this is fine, especially since there’s plenty of work to do outside of Rails. It’s interesting to have such a cross-section of reactions to it all working together.

Interfacing with Google

Since our project employs Google Maps to display store locations, we need to interface with their API. More importantly, we need to load latitude and longitude coordinates of the stores into our own database. Since we all quickly agreed that we didn’t want to do that by hand, we worked out an alternate solution.

The interface to add a new store to the database employs Google Local Search to find stores that are already in Google’s system, and therefore already have all of the information we need (except hours). From the search results we get back from Google, we can click a button that then pushes the data up to our own server. Then we go in and set the hours of operation and assign a category. It took a lot of tweaking to get it working right, but after a few hours of Rodolphe working on the client-side Javascript and me working on the server-side receiving end, we got it ironed out.