On Building Software

The Small Pleasure of Info Tables

Most coding approaches don’t have to be big enough where we feel compelled to call them patterns, frameworks, or architectures. Maybe technique is a good label for these unsung heroes.

I love those satisfying little techniques you use over and over again. The ones where you go “that makes sense” but you keep finding yourself rediscovering your happiness with it.

Here’s one such technique I’ve used for years. The subject: Relational data modeling.

When I data model, I generally find myself creating one-to-one mappings between major domain concepts and database tables. Users have a Users table. Accounts have an Accounts table. Projects have a Projects table.

Associative concepts like account memberships (a user’s accounts) and project memberships (a user’s projects within an account) translate cleanly across the code-database divide as well, in the form of associative (or join or many-to-many or intersection or linking or whatever we’re calling them) tables.

For many reasons, you try to keep these big domain concept tables relatively narrow. One of them is simply the drudgery of adding a new column. I shiver at the idea of messing around with the structure of a table that’s been doing its good work for years — thus tempting the fate of the performance gods (because if you’re like me, part of successful index tuning involves a good prayer or two).

Here’s the technique: Every major domain table can benefit from a related extension table which I will call an info table (you might call it an extension table).

A terribly vague name I know. But that’s for good reason. It’s where you store all sorts of one-off informative nuggets about records yet live on the main table. Metaphorically speaking, the kind of stuff you might list as interesting snippets about yourself at a corporate social icebreaker.

The schema of an info table needs three components: A key (string), a value (larger string), and a foreign key back to the related parent table. It’s really then just a simple key-value store with a link back up to the main record.

The Accounts table has a relationship to the AccountInfos table; Users to the UserInfos table; Projects to the ProjectInfos table, and so forth. This goes for the associative tables too. An AccountMembershipInfos table or a ProjectMembershipInfos table come in quite handy.

Having tended to a mature software product for well over a decade now, I find most new features we introduce can be managed in the database by adding key-value records to these tables alone. Rarely do I need brand new tables and even more rarely do I feel it best to widen an existing table.

When software reaches a certain point in its life, most of the new stuff you’re adding is decorative. It’s not this big new spaceship you’re trying to land in the alleyway as much as it is adding shiny new knobs to the spaceship’s dashboard.

The keys for the various info records are managed in code. As simple as a static class of const strings (For example, a static class called ProjectMembershipInfoKeys):

Along with a couple of foundational methods — one to pull a particular entity’s value based on the key and one to upsert an entity’s value based on that key, you have what you need to take care of the storage side of most new features.

So, let’s say I’m adding a new feature that lets someone favorite a project. They see a list of project cards in their dashboard and there’s a star on the corner of them that they can click. Favorites bring those cards to the top of the list.

From the database side, this means we need to store a note (let’s just say a boolean) to mark a project as someone’s favorite. I can add this tidbit as a record into the ProjectMembershipInfos table using a key like “FAVORITED”:

And then, on the storage side, I can add a couple of database-connected methods to favorite or unfavorite the project which basically wrap against those foundational ones.

In this case, I also have a removeProjectMembershipInfos method that I use for unfavoriting. I could’ve also passed in a false parameter.

And, wherever I need to pull this data out, it’s a simple matter of selecting out the requisite ProjectMembershipInfos record for a given project and user with the key “FAVORITED”.

Of course, there’s all sorts of other work to get this feature off the ground — the UI, API, and other code plumbing. But the database work is largely trivial stuff, something I don’t have to reinvent every time a new feature is on the horizon.

Here’s a sampling of some of the other kinds of features, specific to my work on DoneDone, whose main storage work involves info tables. Some store booleans as the value, others might store a guid/code/token, or a comma-delimited list of IDs, or some enum converted to a string.

• Toggling due date notifications for a project (A boolean value for a “DUE DATE NOTIFICATIONS” key off of ProjectMembershipInfos)
• The type of updates you want to get emailed for in a project (An enum value for a “EMAIL SUBSCRIPTION TYPES” key off of ProjectMembershipInfos)
• The channel ID and token of a Slack room connected to a project to broadcast updates to (A pipe-delimited token|channelID string for a “SLACK_TOKEN_AND_CHANNEL” key off of ProjectInfos)
• Whether a project is a “sample” and not a real one (A boolean value for a “SAMPLE_PROJECT” key off of ProjectInfos)
• The generated unique email reply address for an email conversation (A string value for a “CONVO_EMAIL_INBOX_ALIAS” key off of ActionableItemInfos)
• Storing the public API key for a user (A guid for a “PUBLIC_API_KEY” key off of UserInfos)
• Marking whether a user logged on with Google (A boolean for a “LOGIN_VIA_GOOGLE” key off of UserInfos)
• Storing pinned task and conversations for a user on their account dashboard (A pipe-delimited list of IDs on the AccountMembershipInfos)
• Whether an account has the ability to use the “Workflow Jobs” feature (A boolean for a “FEATURE_FLAG__JOBS” key off of AccountInfos)

Not every kind of feature fits well with the info table approach. If some feature requires very large values (where an NTEXT or NVARCHAR(MAX) field is required), I’ll opt for a new column on the parent table or some other new table). Or, if the feature’s value needs to be searched or queried upon, I’d put it somewhere else too — I don’t have indexes on these value columns. The lookups are relegated to the key column and its foreign key alone.

There are a wide range of features I’ve implemented over the years whose database underpinnings live in these simple, gloriously narrow info tables. The best natural fits are the ones where you’ve got the entity in-hand already (or at least its primary key), and you just need that extra “stuff” after the fact.