Geopolitical Consequences of SQL Design

Map of the Barbary Coast by the French cartographers Pierre and Alexander Lapie

Or: the consequences of not using many-to-many relationships

Is Moroccan food African or Mediterranean?  We designed a simple hierarchical model of cuisines that grouped them by broad geographic area.  The purpose of the model was solely to populate a configurable, organized list for a select menu, where smaller areas were visually grouped.


Custom fields in Rails

Photo of train tracks by Julian Hochgesang on Unsplash

What approach do you take when adding dynamic attributes to model objects stored in a traditional database?  Let’s say a bug model with static fields like name and species also needs to support any number of additional dynamic fields specified by different bug collectors.  There are a number of possible solutions to this common problem, so let’s consider the options.

  1. Serialize a hash ({ color: "brown", arms: 2 }) into a column at save, and de-serialize at load.  This serialization would be easy from a configuration standpoint, but would scale poorly and would be rubbish for searching on key values.  Rejected.
  2. Integrate a NoSQL datastore like redis.  A NoSQL datastore would offer speed and a task-appropriate API at the expense of integrating, configuring, and maintaining a new storage component for a minor feature.  Reasonable, but not appropriate in the context.
  3. Add a series of “CustomField#{n}” columns to the bugs table.  No, just no.  Don’t make me cry.
  4. Dynamically add columns to the bugs table (or to per-collector dynamic tables) when new custom fields are defined.  These columns would allow strict type validation at the DB layer, but would limit the total number of columns and would be difficult to maintain with standard ORMs.  Decidedly too much trouble.
  5. Design a standard relational mapping from the bugs table to a custom field table with key and value columns.  This approach is widely used as it fits a standard RDBMS schema.  Its only obvious downside is increasing the number of table rows and objects that need to be managed and read.
  6. Use PostgreSQL’s hstore extension, which provides a key/value hash column type.  If PostgreSQL is already your RDBMS or is an easy switch, using hstore essentially mixes in a NoSQL approach with minimal extra configuration.

Let’s consider the custom field table (cftable) and PostgreSQL hstore (hstore) approaches in more detail, in the context of an application running on Ruby on Rails. (more…)