House cleaning

Last night, I completely re-wrote my shopping list serialization code.  The old code worked fine, but I just didn’t like the design that much.  With the old design, I used the JSON encoder to serialize the entire shopping list class as one “blob” and stick it in a text field in the database.  This was pretty good for saving, but when the user wanted to add a single item to their shopping list, I’d have to deserialize the existing list, add a single item, then serialize the entire list again.  Removing an item entailed the same ordeal.  Plus, being a serialized text field means performing any sort of query on this data is virtually impossible.

The new design is a bit more straight forward.  I use one table row per shopping list item, with a unique constraint on any user/ingredient pair.  The amounts are always stored in the default unit type of that ingredient.  I do this because this design perfectly matches that of the “user pantry” schema, which has a number of advantages.  Mainly, my “shopping list balancing” code can now be run entirely in SQL.  I can loop through the overlap of pantry/shopping list ingredients and “balance” each row, all with no middleware code since I never have to convert between units.

This design also makes updates and deletes much more efficient.  An update is simply looking for the existence of a single row in the table, and incrementing the value or creating said row.  A delete is a single SQL DELETE statement.

Though this change wasn’t critical for the beta release, there was one incentive that drove me to get this out of the way.  It would virtually be impossible to “upgrade” any existing shopping lists from using JSON serialized data to a fully normalized row based representation.  Sure, I could write a program that cracked open each blob and created the rows, but I’d rather not have to mess with that.  For the Alpha site, I’m perfectly content deleting everyone’s shopping list when I apply the new schema.

When triaging bugs and to-do list items, it’s always good to look down the road and weigh the impacts of putting off potentially destabilizing changes.