<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[A Place For Everything - Medium]]></title>
        <description><![CDATA[The Factbook blog - Medium]]></description>
        <link>https://medium.com/all-the-things?source=rss----f1c4348ac90d---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>A Place For Everything - Medium</title>
            <link>https://medium.com/all-the-things?source=rss----f1c4348ac90d---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 15:50:20 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/all-the-things" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[A web application with no web server?]]></title>
            <link>https://medium.com/all-the-things/a-web-application-with-no-web-server-61000a6aed8f?source=rss----f1c4348ac90d---4</link>
            <guid isPermaLink="false">https://medium.com/p/61000a6aed8f</guid>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[software-architecture]]></category>
            <category><![CDATA[software-development]]></category>
            <dc:creator><![CDATA[Herb Caudill]]></dc:creator>
            <pubDate>Mon, 24 Dec 2018 21:16:01 GMT</pubDate>
            <atom:updated>2020-07-09T11:22:45.492Z</atom:updated>
            <content:encoded><![CDATA[<h4>In a traditional client/server/database web app, so much effort goes into transferring and translating data between layers. Can we eliminate unnecessary complexity with a peer-to-peer design?</h4><p>Things have changed a lot in the decade since the last time I built a web application from the ground up. It used to be you picked a framework (ASP.NET, Rails, etc.) and learned it inside out. Now, as a result of the open source explosion in the JavaScript ecosystem, it’s your job to put together a mosaic of frameworks.</p><p>On the one hand, we’ve finally arrived at the promised land of code reuse. If you’re into programming productivity, no Agile methodology or VS Code extension compares to a technique I like to call <strong>“importing code that someone else wrote.”</strong> What a time to be alive!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gaBkIuIaga8YOm4uajxEdQ.jpeg" /><figcaption>Used with permission. Original <a href="http://www.commitstrip.com/en/2015/09/16/how-to-choose-the-right-javascript-framework/">here</a>.</figcaption></figure><p>On the other hand, the framework anxiety depicted in <a href="http://www.commitstrip.com/en/2015/09/16/how-to-choose-the-right-javascript-framework/">this CommitStrip cartoon</a> has multiplied into a fractal landscape of decisions both big and small.</p><p>Say you start by picking a front-end framework. For this project, React looks like an easy choice. It’s one of those rare technologies that makes everyone using it feel like a <a href="https://www.amazon.com/dp/B00VAUIM18/ref=dp-kindle-redirect?_encoding=UTF8&amp;btkr=1">badass</a>.</p><p>But React is just a rendering engine; its scope is minimalist by design. The ecosystem around it is vast and energetic, evolving at a dizzying speed. There are <em>lots</em> of choices to make and lots of alternatives at each juncture. And no matter how much time you put making educated decisions, there will probably be something better next week.</p><p>At some point, though, you have to stop thinking and start coding. I’ve spent the last couple of months experimenting with different approaches to different parts of the puzzle, and the outlines of an overall architecture are starting to take shape in a way that I’m really excited about.</p><p>My starting point is the way <a href="http://www.devresults.com/">DevResults</a> works, so even though that’s a decade-old codebase, let’s take a look at its architecture. It’s a server application written in C#, a SQL database, and an Angular front end. So something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1X5LcMfZJSSrHidOJ9kkBQ.jpeg" /><figcaption>Nothing surprising here — this is a very standard architecture for web apps of a certain age.</figcaption></figure><p>There are several sources of inefficiency here:</p><ul><li>JavaScript, C#, and SQL are very different worlds, and some large fraction of the code we write and debug is just about the mechanics of <strong>unpacking information from one form and repackaging it into another</strong>.</li><li>Our <strong>data model has to be restated repeatedly</strong> in multiple different formats.</li><li>Each of these layers includes some logic, and it’s <strong>hard to avoid duplicating some logic</strong> across more than one layer.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zPg5iHnSZObybNYs8YwJRg.jpeg" /><figcaption><strong>Kind of a mess: </strong>Logic expressed in 3 languages, data model expressed lots of different ways.</figcaption></figure><p>Now, DevResults was first written in 2009, and changes to the code since then have had to be made in a cautious and incremental way in order to avoid disrupting people who actively use the software. We’re starting with a clean slate now, so what should we do differently?</p><h4>JavaScript everywhere</h4><p>The first step to simplify this is to use a single language for the codebase. In 2018, the only reasonable choice is JavaScript. We can use Node.js on the back end, and that way bits of logic that are shared between the client and the server only have to be written once.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YgjqMrYPSpoclgBttxLEJw.png" /><figcaption><strong>Better</strong>: Server-side JavaScript lets us write code around the data model just once, and run in both places.</figcaption></figure><h4>JSON everywhere</h4><p>We can also use a NoSQL database like MongoDB, which accepts and returns JSON data. So our data can move from client to server to database without having to be translated.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xL9jtG1E_mmS4K5QtZv1mQ.png" /><figcaption><strong>Better</strong>: GraphQL for requesting data, JSON for representing data.</figcaption></figure><p>Another simplification is to use GraphQL instead of REST to communicate requests for data. In fact, if we use <a href="https://www.apollographql.com/docs/react/essentials/local-state.html#queries">Apollo’s client framework</a>, we can use GraphQL to query local state and locally cached data in addition to server data. That leaves us with just two representations of the data model, which is a big improvement.</p><p>So far, so good. There’s nothing revolutionary here; in fact something like this architecture is rapidly becoming the norm for new projects.</p><h4>And yet…</h4><p>But when I started playing with this in a couple of toy apps, I was <strong>still frustrated by the number of moving parts</strong>, and the consequent amount of ceremony required just to do simple things.</p><ul><li>Data <strong>storage </strong>is provided by the database. But since the client needs to work offline, it needs to provide storage also. That means you need to write two data layers — one for the client to work with local data, the other for the server to work with the database.</li><li>Application <strong>logic</strong> traditionally lives on the server. But if the client needs to work offline, some or all of that logic needs to be on the client as well.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G9qSOV9Vz_joyz3O2fDn7Q.png" /><figcaption>Is there a good reason for all of this duplication?</figcaption></figure><p>So we’re duplicating logic between the server and the client, and duplicating storage between the database and the client.</p><p>But when I really ground to a halt was when I started thinking about how to support a user who is disconnected from the server.</p><h4>Thinking offline-first</h4><p>We all spend a certain amount of our time offline or on spotty networks. For those of us who live in bandwidth-saturated cities in the rich world, that’s primarily when we’re traveling. For many DevResults customers, who are trying to get things done in remote corners of some of the poorest countries on earth, working offline is a fundamental necessity.</p><p>Excel, for all its faults, doesn’t care whether you’re offline or online. If we really want to liberate people from spreadsheets, we need to be able to provide first-class support to the offline scenario.</p><p>There are two big challenges with building an app that works offline:</p><ul><li>It has to work when no server is available (duh), which means that it needs to have a <strong>full local copy of the data </strong>that it needs.</li><li>When the app does go back online, it needs to <strong>reconcile changes </strong>that have been made locally with changes that have been made elsewhere.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o5VhyhnRcla29wn92m6eBg.png" /><figcaption>The standard browser/server/database model isn’t well-suited to offline scenarios.</figcaption></figure><p>Long story short, this isn’t the architecture you would start with if offline usage is a priority. It might be possible to bolt on offline capabilities after the fact, but it wouldn’t be easy and it wouldn’t be pretty.</p><p><strong>Since we are starting from scratch, we can do better.</strong> But we’ll have to rethink things at a more fundamental level.</p><h4>How did we get here?</h4><p>This is the sort of kludge that you get when you’re at a very specific and awkward juncture in the history of technology.</p><p>Specifically: Since the invention of the computer, the pendulum has swung every few years from putting intelligence on the server, to putting it on the client, and back again. Most recently, we’ve gone from <strong>dumb terminals</strong> running software on a mainframe, to <strong>PC operating systems</strong> with software installed, to <strong>software as a service</strong> delivered in the browser.</p><p>The original web applications ran entirely on the server, and the browser was just a dumb terminal. Each interaction caused the server to re-render a fully-formed HTML page and send it to the browser. In 2005 <a href="https://adaptivepath.org/ideas/ajax-new-approach-web-applications/">AJAX became a thing</a>, allowing us to avoid some of those round trips. Ever since then we’ve been <strong>moving intelligence back to the client</strong> in bits and pieces.</p><p>More than a decade later, you can do pretty much anything you want within a browser, and even more if you give the browser big-boy pants by wrapping it in <a href="https://electronjs.org/">Electron</a>. But we’re still living with a lot of assumptions that come from the days of server-side rendering.</p><h4><strong>What if we <em>only </em>had a client?</strong></h4><p>Let’s take a step back and think about what a “web application” with no web server would look like.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aFIQS24R8OEoKxavyqhG-w.png" /></figure><p>The most obvious answer is that <strong>we need the server to collaborate with others</strong>. If you’re the only one involved in whatever you’re working on, you may not need a web app. But if you’re working with a team of two or more people, you want some way of keeping everyone on the same page, and centralizing the data on a server is an obvious way of doing that.</p><p>So, yes, this would be a very simple architecture:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z6G6L-F5JwF5Tb_GfgMZWw.jpeg" /><figcaption>Simple, but not very useful.</figcaption></figure><p>But Herb, I hear you saying: This is of no use to us if Client A (Alice) and Client B (Bob) need to collaborate.</p><p>So let’s put on our “<a href="https://books.google.es/books?id=e75G0xIJju8C&amp;lpg=PA121&amp;ots=6SOMylhUDc&amp;dq=pretend%20it%27s%20magic&amp;pg=PA121#v=onepage&amp;q=pretend%20it&#39;s%20magic&amp;f=true">pretend it’s magic</a>” hats.</p><p>Let’s just imagine for the moment that we had a magical way of automatically and instantaneously synchronizing Alice’s data with Bob’s data, so that when they’re both online, they’re both always looking at the same thing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7OAbR5BxeEOk2kLRLfR-cg.gif" /><figcaption>Simple, but you have to believe in magic.</figcaption></figure><p>If we had that magical piece, then we’d truly be able to eliminate duplication in our codebase.</p><p>But wait, you say! This peer-to-peer setup requires Alice and Bob to be online at the same time. If Alice makes some changes and then goes offline, Bob won’t see them the next time he comes online.</p><p>OK, no problem — we can just fire up a client on a computer that no one uses, and leave it running all the time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ocuzV2m7CD651kyAU-TgGQ.png" /></figure><h4><strong>Congratulations, we’ve just re-invented the server!</strong></h4><p>Except this server is really just a special instance of our client — one that no one uses directly, and that lives on high-availability infrastructure.</p><p>Our <strong>headless-client-as-server</strong> doesn’t require a separate codebase. Compared to a traditional server, it can be hosted in an inexpensive, highly redundant, and infinitely scalable way: Each instance could live inside an lambda function, automatically scaled as needed, and backed by a simple key-value persistence layer.</p><p>OK, that’s great, but at some point we’re going to have to have to come up with a solution that keeps everyone’s data consistent without relying on magic.</p><p>As you might suspect, data replication and synchronization is a problem that a lot of smart people have been thinking about for many years; so not only do we not have to rely on magic, but we don’t have to invent anything new.</p><h4>Mutable data, immutable data, and the meaning of truth</h4><p>The solution hinges on a different conception of data from the one I’ve always worked with.</p><p>The databases I’ve always worked with treat data as <strong>mutable</strong>: If you need to update data, you replace what was there with something new. So when I moved from Washington DC to Barcelona, some database might have reflected the change by replacing a value in the City column for my record:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Rnrif27ZBSRpjrrwdnStIA.gif" /></figure><p>In this case, you could go back and look at the <strong>log </strong>of changes to see, for example, that an entry was made in 2012 stating that I lived in Washington DC, and that in 2016 that entry was updated to Barcelona.</p><p>In fact, using that log, we could theoretically reconstruct the state of the database at any point in time — but it would be clumsy. In a traditional database, the <strong>state </strong>of a given table <em>right now </em>is our <strong>source of truth</strong>, and the change logs are ancillary metadata.</p><p>But suppose you have two replicas of this database, and they’re both intermittently online. At some point we compare them, and one says I live in Washington while the other says I live in Barcelona. How do we know which one is true? Without any additional information, we have no way of knowing.</p><p><strong>This perspective is what makes replication and synchronization complicated. </strong>Our source of truth is a moving target.</p><h4>The inside-out database</h4><p>What if we reverse our perspective, though, and think of the <em>change log </em>as our <em>primary </em>data source? In that case a snapshot of the data at any point in time is just a <strong>pure function </strong>of the event log.</p><p>The event log shows these two facts:</p><ul><li>In 2012, Herb said he lived in Washington DC</li><li>In 2016, Herb said he lived in Barcelona</li></ul><p>Both of these statements are true facts, and we can easily query them to derive a picture of the state of the world at any given moment t. Now we can see that the <em>current</em> state of the world is just a special case, where t = <strong>now</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LmJojjeU8Paq84-UXrIwVQ.gif" /></figure><p>With this perspective, much of the complexity around replication and synchronization just disappears. <strong>CRUD</strong> (create/read/update/delete) is now just <strong>CR</strong>, because updates and deletes are just another kind of new information. It’s much easier to merge two event streams than it is to merge two conflicting point-in-time snapshots.</p><p>Now, this all just clicked for me, and I’m super excited about it. But it’s nothing new: The abstraction of append-only logs and immutable data stores has been with us for a while.</p><ul><li><strong>Double-entry bookkeeping</strong> is perhaps the original append-only log. If there’s an error in a previous entry, we don’t go back and correct it in place: We make a new adjusting entry.</li><li>This is exactly how <strong>Git, Mercurial, </strong>and similar source-control management systems solve the problem of asynchronous, distributed collaboration: by storing a sequence of changes, and deriving point-in-time snapshots from those changes.</li><li><strong>Redux</strong>, the widely-used state container for JavaScript apps, treats data as immutable, which makes “<a href="https://www.youtube.com/watch?v=xsSnOQynTHs">time travel</a>” possible and makes it easy for frameworks like React to detect updates.</li><li><strong>Conflict-free replicated data types</strong> <strong>(CRDTs) </strong>are data structures that can be replicated across multiple computers, that can be concurrently updated, and that resolve any resulting conflicts in a predictable, consistent way.</li><li>And, of course, the <strong>blockchain </strong>is a distributed append-only log, with the added twist of cryptographically-verified consensus to verify correctness.</li></ul><p>I’m going to focus on the next-to-the-last item in that list — CRDTs — because they abstract away the problem of reconciling conflicting changes.</p><h4>CRDTs and Automerge: Indistinguishable from magic</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/746/1*lfktB_rBOlQN-vR0vcT_mA.png" /></figure><p><a href="https://github.com/automerge/automerge">Automerge</a>, a JavaScript implementation of a CRDT, promises to be the magical automatic synchronization and conflict resolution layer we’re looking for.</p><p>Automerge is essentially a wrapper for JavaScript objects that enforces immutability, provides an interface for committing changes, and automatically resolves conflicting changes.</p><p>The simplest way to think about Automerge is that it’s <strong>just like Git, for JSON data — except without merge conflicts.</strong></p><ul><li><strong>Just like Git,</strong> each user has a local copy of the data, and can commit changes whether or not they’re online.</li><li><strong>Just like Git, </strong>when there’s a network connection, changes can be pushed, merged, and pulled so that everyone ends up with the same state.</li><li><strong>Just like Git</strong>, users can choose to work <strong>alone</strong>, or to collaborate <strong>peer-to-peer</strong>, or via a <strong>central hub</strong>.</li></ul><p>However:</p><ul><li><strong>Unlike Git,</strong> merge conflicts are <strong>automatically resolved</strong> in an <strong>arbitrary but predictable </strong>way, so that everyone ends up in the same state without any further intervention (hence the “Auto” in “Automerge”).</li></ul><p>The idea of automating conflict resolution is the part of this that’s most likely to raise eyebrows, so let’s look at that a bit more closely.</p><ul><li>First of all, a CRDT uses an algorithm to resolve conflicts that is <strong>arbitrary </strong>and <strong>deterministic</strong>. To be clear, it’s not random: If it were, then different users could end up in different states. It’s also not based on any clocks, since we can’t ensure that any two users’ clocks show the same time. For example, you might use the sort order of a hash of the change data to determine the order “wins”. The only requirement is that all users can independently make the calculation and arrive at the same result.</li><li>Conflicts are resolved in a <strong>non-destructive </strong>way, preserving information about the conflict as metadata. This way, if needed, the application can apply its own conflict-resolution logic after the fact, or surface conflicts to a user for human resolution.</li><li>Conflict resolution is <strong>rarely necessary</strong> in most use cases. If two different objects are modified, or different properties of the same object are modified, there is no conflict. If two users simultaneously insert or delete items in an array (or characters in a text document), all insertions and deletions are preserved. There is only a conflict when there are concurrent changes to the same scalar property on the same object.</li></ul><p>So the overall idea starts to look like this:</p><ul><li>Instead of a traditional browser/server/database stack, we have <strong>a single application that can work as a standalone thing</strong>, persisting its data in a local or remote key-value store, and optionally synchronizing its data with other instances.</li><li><strong>Real-time background synchronization</strong> is provided automatically and unobtrusively by <strong>Automerge</strong>.</li><li><strong>Instead of a server, we have an always-available headless peer</strong> living in a lambda function. If we define a “team” as our permissions boundary, it might make sense to just spin up one lambda per team.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I_E1eXQgcFwiGrllvm4NhQ.png" /></figure><p>This is a very different design from the ones I’ve worked with in the past. It’s not the way most software is being written today. But I’m convinced that many applications will be architected this way in the future. The advantages are hard to ignore:</p><ul><li>Users can work with the application <strong>offline or online</strong>.</li><li>The application is always working directly with local data, <strong>eliminating network latency </strong>from most use cases.</li><li>Users can decide whether they want to <strong>work alone</strong>, collaborate on a <strong>peer-to-peer</strong> basis, or use a server as a <strong>central hub</strong>.</li><li>If a central server is needed, it is <strong>cheap</strong>, low-maintenance, and super scalable.</li><li>Users can easily <strong>roll their data back</strong> to any point in time, or review an audit trail of who made what changes when.</li><li>Developers can work with a vastly <strong>simpler stack</strong>, with no duplication, no impedance mismatches and fewer possible points of failure — making it easier, cheaper, and faster to develop new functionality.</li></ul><p>There are trade-offs involved that are going to be deal-breakers for some scenarios. For example, this setup doesn’t make sense for <strong>big datasets</strong> — specifically any datasets that are too big to store on a single computer. In practical terms, today, this probably means an upper limit somewhere between 10GB and 1TB, depending on the target market. That upper limit will rise exponentially in a predictable way; but this will never be a “big data” solution.</p><p>For the purposes of this application, that’s OK. Remember, we’re talking about a tool to allow teams and individuals to organize <em>their own information</em>. <a href="https://medium.com/all-the-things/a-single-infinitely-customizable-app-for-everything-else-9abed7c5b5e7">Our competition is a bunch of spreadsheets</a>.</p><p>Also, I haven’t mentioned authentication and permissions. Unlike a traditional database, Automerge assumes those concerns are handled elsewhere. I have some thoughts, but I’ll leave those to a later post.</p><p>Is this insane? Has it been done before? Let me know what you think!</p><h3>Further exploration</h3><p>In <a href="https://writings.quilt.org/2014/05/12/distributed-systems-and-the-end-of-the-api/">Distributed Systems and the End of the API</a> (2014), Chas Emerick points out that network API models — whether RPC, SOAP, REST (or now, GraphQL) — are fundamentally unsuited to distributed systems, and that many API use cases can be solved using CRDTs instead.</p><p>The earliest and clearest explanation of the advantages of immutable event logs I found is in the article <a href="http://nathanmarz.com/blog/how-to-beat-the-cap-theorem.html">How to beat the CAP theorem</a> (2011) by Nathan Marz. Martin Kleppman takes a very similar line in the talk <a href="https://martin.kleppmann.com/2015/03/04/turning-the-database-inside-out.html">Turning the database inside-out with Apache Samza</a> (2015).</p><p>Martin Kleppman is also the author of <a href="https://github.com/automerge/automerge">Automerge</a>, which he presents in a very accessible way in the talk <a href="https://www.youtube.com/watch?v=PHz17gwiOc8">Making servers optional for real-time collaboration</a> (2018).</p><p>Automerge was built under the auspices of <a href="https://www.inkandswitch.com/">Ink and Switch</a>, an industrial research lab founded by Adam Wiggins, one of the co-founders of Heroku, along with Heroku veterans Peter van Hardenberg and Mark McGranaghan.</p><p>Ink and Switch has also created a series of proof-of-concept apps using Automerge:</p><ul><li><a href="https://github.com/automerge/trellis"><strong>Trellis</strong></a>, a peer-to-peer Trello clone</li><li><a href="https://github.com/automerge/pixelpusher"><strong>Pixelpusher</strong></a>, a collaborative drawing tool</li><li><a href="https://github.com/inkandswitch/pushpin"><strong>Pushpin</strong></a>, a collaborative corkboard app</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=61000a6aed8f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/all-the-things/a-web-application-with-no-web-server-61000a6aed8f">A web application with no web server?</a> was originally published in <a href="https://medium.com/all-the-things">A Place For Everything</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data types for humans]]></title>
            <link>https://medium.com/all-the-things/data-types-for-humans-ea2dd63a19db?source=rss----f1c4348ac90d---4</link>
            <guid isPermaLink="false">https://medium.com/p/ea2dd63a19db</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[ui]]></category>
            <category><![CDATA[software]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Herb Caudill]]></dc:creator>
            <pubDate>Sun, 23 Dec 2018 21:16:01 GMT</pubDate>
            <atom:updated>2018-12-23T21:16:00.967Z</atom:updated>
            <content:encoded><![CDATA[<h4>Today’s databases force us to think about data in terms of what’s important to the computer. We can do better.</h4><p>A key difference between a table in a <strong>spreadsheet </strong>and a table in a <strong>database </strong>is that <strong>database columns are typed:</strong> If you’re storing dates in a column, all the values in that column have to be dates.</p><p>Consistent data types are a big part of what makes a database a database — and what makes a database more powerful than a spreadsheet.</p><p>But, <a href="https://medium.com/all-the-things/a-single-infinitely-customizable-app-for-everything-else-9abed7c5b5e7#40cc">as we’ve seen</a>, data types are also a key point of friction for non-technical users, because they have to be nailed down too early in the process.</p><p>To get the best of both worlds, we want to avoid being heavy-handed: We don’t want to force Celeste to lay out her table schema in advance, but we do want to be smart and try to figure out her intentions. So if she types in a date, we’ll assume this is a date column. We’ll pick up the date format from the way she’s typed it in. And if she changes her mind and needs to change a column from one type to another, we want to make that as effortless as possible.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OZBN_m-HIuntmyIsHJfPyQ.png" /><figcaption>We want a column to pick up the date format from the way it’s typed in — just as it would in Excel.</figcaption></figure><p>Before we go any further with this, though, let’s talk about data types.</p><h4>Towards humane data types</h4><p>I propose that the friction here doesn’t necessarily all come from having to choose a type in advance. The problem is also that most database systems force you to think about types <strong>from the computer’s perspective</strong>.</p><p>You only have a few fundamental types to choose from (text, number, date, boolean) and within those categories you have to make some commitments about how much space the computer should set aside. So for example, if you’re going the store numbers in a column, the system really wants you to make a lot of decisions in advance about size, precision, and so on.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8GaD1XKAYTfiyZ8MLwaaBg.png" /><figcaption>SQL Server’s datatypes. Do you want <strong>int</strong> or <strong>bigint</strong>?<strong> </strong>Any programmer worth her salt can tell you a story about making the wrong choice and coming to regret it.</figcaption></figure><p>Human beings have different concerns. Does this number represent an amount in Euros or in Dollars? Or is it a measure of weight, or length, or area? What if it’s a distance in kilometers, but I want to show it in miles to some users? Does this text represent phone numbers or RGB colors?</p><p>With today’s databases, if you care about any of those things, <em>you’re</em> going to have to do the heavy lifting.</p><p>So basically you, the human, have to give a lot of thought to implementation details that should be the computer’s concern; but then when you want the computer to help you out with silly things like currencies or countries or weights or measures or oh, I don’t know, BASICALLY ANYTHING HUMANS CARE ABOUT, you’re on your own.</p><p><strong>I think that’s the opposite of how it should be.</strong></p><p>So what data types would actually be helpful to Celeste?</p><p>I think these categories ought to be front and center:</p><ul><li>Dates and times</li><li>Physical measures: length, distance, weight, etc.</li><li>Names, addresses, and other multi-part human artifacts</li><li>Countries, states, currencies, languages, and other canonical lists</li><li>References to other things in the same system</li></ul><p>Let’s look at each of these in turn.</p><h4>Dates and times</h4><p>The passage of time is of fundamental concern to us humans, perhaps owing to the fact that we all die.</p><h3>Nihilist Arby&#39;s on Twitter</h3><p>It&#39;s your first time at arbys. What should you get? You should get used to a lifelong creeping dread culminating in your meaningless death</p><p>Most computer systems have something like a datetime datatype that allows you to identify an instant in time, down to the millisecond: for example, Wednesday, June 7, 2018, 9:45:23.1234 (Greenwich Mean Time).</p><p>And maybe that is, in fact, what we are interested in.</p><p>But it’s often either a <strong>date with no time</strong> <em>or</em> a <strong>time with no date:</strong></p><ul><li>June 7, 2018</li><li>9:45 AM</li></ul><p>Or, it could be a related, but distinct, concept such as:</p><p>A <strong>duration</strong>:</p><ul><li>30 milliseconds</li><li>5 days</li><li>42 years</li></ul><p>A <strong>period of time</strong>:</p><ul><li>the year 2007</li><li>the month of May 1997</li><li>Quarter 1 of 2019</li></ul><p>A standalone <strong>day of the year</strong>, <strong>month</strong> or <strong>day of the week:</strong></p><ul><li>every March 16</li><li>every February</li><li>on Tuesdays</li></ul><p>Today’s databases are of very little help with managing these constructs.</p><p>Now, there are libraries in most programming languages to help <em>programmers </em>represent these concepts cleanly. For example, the <a href="https://js-joda.github.io/js-joda/">js-joda</a> library for JavaScript (based on the <a href="https://github.com/JodaOrg/joda-time">joda-time</a> package for Java) does a great job. But database tools supposedly designed for <em>non-programmers — </em>like Access or Airtable — mostly leave those users to struggle unassisted with the database’s raw data types.</p><p>This is going to be a recurring theme: There are so many great programming libraries and frameworks and concepts that have made things very clean and simple and straightforward for programmers. But no one has taken these ideas one small step further and wrapped them up in a form that makes them usable to civilians!</p><h4>Physical measures: length, distance, weight, etc.</h4><p>Another amusing human idiosyncrasy is that we have multiple incompatible systems to measure the physical world.</p><p>The good news is that the rules for converting, say, 5&#39;8&quot; to 172.72 cm are well-known and involve simple math. Computers are better at simple math than people are. A thoughtfully designed system would take advantage of that fact to transparently take care of those conversions — so that one user could enter data in feet and inches, while another users would see the same data expressed in centimeters.</p><p>Again, there’s no shortage of free libraries to help with these concerns: For example, the <a href="https://github.com/ben-ng/convert-units">convert-units</a> library for JavaScript can handle not only everyday things like length, distance, weight, and temperature — but also more specialized physical measures like acceleration, frequency, or volume flow rate. But again, unless you have the wherewithal to write your own software, these don’t do you much good.</p><h4>Names, addresses, and other multi-part human artifacts</h4><p>If you’ve ever created a table of contacts in Excel, the first thing you’ve had to deal with is human names.</p><p>Perhaps you started with a single “<strong>name</strong>” column. Then, you realized you wanted to sort by last names, so you figured out how to automatically split a full name into a <strong>first name</strong> and a <strong>last name</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/630/0*Uny5s1vCSrUEtHCE.png" /></figure><p>But then start seeing names like these…</p><ul><li>J. Shane Kunkle</li><li>Herbert “Herb” Caudill III</li><li>Josep Puig i Cadafalch</li><li>Dr. John P. Doe-Ray CLU, CFP, LUTC</li><li>Brigadier General Zebulon Pike</li></ul><p>…and you decide to call in sick and spend the day under the covers.</p><p>And then there are addresses. Do you need one or two address lines? Three? Do you support international addresses? Do you need to break out street numbers? Apartment numbers?</p><p>What would the “<strong>pretend it’s magic</strong>” solution for handling names and addresses look like?</p><p>What I’d like would be to just paste them in, as-is — but have the computer figure out their component parts. That way I’d still be able to sort people by last name, or pull out their initials or their preferred given names. And I’d be able to display addresses in a standard format, or group them by zip code or by country.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G9H2sL8gsDaKF-TjgKIrgg.png" /></figure><p>The good news is that we don’t need magic in this case, neither do we need to write a lot of code. Parsing and normalizing human names is hard, but it’s mostly a <a href="https://www.google.es/search?q=parse+human+names">solved problem</a>. Same for <a href="https://github.com/openvenues/libpostal">addresses</a> .</p><p>The same approach applies to other fascinating human artifacts like <a href="https://github.com/catamphetamine/libphonenumber-js">phone numbers</a>, <a href="https://github.com/bgrins/TinyColor">RGB colors</a>, and <a href="https://github.com/beaugunderson/ip-address">IP addresses</a>. There are tons of existing services and open-source libraries to choose from for each of these. There’s no reason to force people to come up with their own home-grown solutions to these basic needs.</p><h4>Countries, states, currencies, languages, and other canonical lists</h4><p>Another hassle you might have dealt with in your contact spreadsheet is the problem of inconsistently entered names — for U.S. states, countries, and so on.</p><p>If you’re given this data and you want to do anything at all with these columns, you have to go through and make them consistent by hand.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O_msxV9aPmK-hM47iyux7Q.gif" /></figure><p>And if you want to make a spreadsheet that forces people to choose from as set of options so that it stays clean, you have some hoops to jump through.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yd9gk-MNC8I7UQRBORrCyQ.png" /><figcaption>If you know how to make this happen, you’re in the elite 1% of spreadsheet power users.</figcaption></figure><p>A humane system would take into account that(a) <em>this is a really common scenario! </em>and (b) <em>we have standard lists for these things!</em> — and make it easy to create a column with a datatype of Country or US State (or Canadian Province, or Kyrgyz Oblast, or what have you). Same goes for Currency and Language.</p><h4>References to other things in the same system</h4><p>One of the big limitations of spreadsheets is that it’s hard to link data from one table to another. Relational databases have this capability as a core strength — as the name implies — but as we’ve seen, most databases force us to think in an indirect and abstract way that doesn’t come naturally to most people.</p><p>This point — where Celeste realizes that what goes in a cell is actually a reference to a complex object that is defined elsewhere — is one of two key mind-melting junctures.</p><p>This shouldn’t be complicated.</p><p>Suppose I have a table of Classrooms and a table of Teachers. I want to assign a Teacher to each Classroom, so I make a column called Teacher. What’s the field type of that column?</p><p>It’s a Teacher.</p><p>What it’s <strong>NOT</strong>: A Link, a Reference, a Foreign key, or any other nerdy term that focuses attention the underlying implementation and makes it scary to civilians. Celeste shouldn’t have to decide whether this will be stored in a normalized or denormalized way. Celeste shouldn’t have to know an embedded document from a pointer. These are implementation details and they shouldn’t leak through to the UI.</p><p>Suppose I’m building a system that includes Tasks, Projects, Companies, Customers, and Employees. I should be able to treat each of these as a data type, and see them listed right there with all the other data types we’ve discussed. What goes in this field? A Company. Or a list of Employees.</p><p>Again, we’ve made this kind of thing straightforward and intuitive for ourselves as programmers. We’ve been living in an object-oriented world for over three decades now. We the have built-in <strong>primitives</strong>, yes, but we can define our own <strong>custom objects</strong>. Objects have <strong>properties </strong>that are in turn primitives or objects. <strong>This elegantly recursive paradigm makes it possible for us to model pretty much any real-world concept we can dream up, in all its detail and complexity.</strong></p><p><em>So why don’t we put these tools in the hands of users?</em></p><p>Instead we restrict them to stupid grids and give them a handful of stupid datatypes to choose from. It ain’t right.</p><h4>Collections of things</h4><p>The elegance of the object model depends, crucially, on our ability to represent <strong>arrays of objects </strong>in addition to <strong>individual objects</strong>. So there might be multiple Employees working on one Project, or multiple Customers in the same Company.</p><p>This is the second mind-melting juncture for Celeste: How do we fit a list of things — or a whole other table — into this little cell?</p><p>Again, as programmers we have no problem modeling this. You can have a thing, or you can have an array of N things.</p><p>In the latter case, it’s easy to end up with a clunky UI. But that’s a solvable problem.</p><p>What’s required is a willingness to <em>try</em>, and a willingness to <em>stop infantilizing the user.</em></p><h4>Stop treating civilian users like babies</h4><p>On that last point, a brief rant.</p><p>Since the early days of personal computing, we’ve made great progress towards making software easier to use. We’ve learned to say <a href="https://signalvnoise.com/posts/1626-the-most-powerful-word-is-no">no to new features</a>, we remind ourselves to <a href="https://www.amazon.com/Dont-Make-Think-Revisited-Usability/dp/0321965515">Don’t Make Me Think</a>, and we aspire to create software that <a href="https://en.wikipedia.org/wiki/Unix_philosophy">does just one thing well</a>.</p><p>That’s all healthy and positive — for the most part.</p><p>But I feel like one result of that has been that we software makers have collectively and individually ratcheted down our level of ambition. We’ve internalized a condescension towards end users that has led us produce more focused applications that do less and less, on the theory that anything more would overwhelm our poor customers.</p><p>As a result, there’s a huge chasm these days between what you can accomplish with user-facing tools and what you can accomplish by writing code.</p><p>Turns out, <strong>there are lots of intelligent non-programmers out there</strong>. There was a time — I know because I remember it — when there was tons of competition for that market. Throughout the late 1980s and early 1990s, there were dozens of innovative database systems designed for non-programmers to use: dBase. Hypercard. FileMaker.</p><p>Part of what happened, I think, was an accident of history: After Microsoft Office steamrolled the “office productivity” segment of the software industry, we were left with just two viable products in the user-facing database space: Excel and Access. Access has never sparked much enthusiasm or creativity, on account of it sucks and has always sucked. Excel has proved to be approachable and versatile enough that, in spite of not being a database, it has become the general-purpose database of choice. In doing so, it’s almost completely suffocated innovation in this space.</p><p>But those users are still out there, and I think they can be trusted with the concepts of arrays and objects and recursion.</p><p>It’s our job, as programmers, to offer up these facilities in a way that maps naturally to people’s intuitions; and there are a million ways to get that UX wrong. It’s our job to hide everything but what’s essential, to provide affordances, to avoid exposing distracting or intimidating implementation details. But it can be done, and it should be done, because it’s not right that all that creative energy is being wasted on twisting Excel into unspeakable contortions.</p><p>I’m going to stop there for now. Hopefully we’re a bit closer to having a tangible idea of what this tool might look like.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9d8mCVO-6fPfawXnPRXzTA.png" /><figcaption>One possible way of presenting data types.</figcaption></figure><p>We want to make a tool that serves as a platform for humans to organize information for themselves and for teams they work on. To get there, I think we need to give a lot of thought to the types of data that humans are likely to organize. It believe it will be crucial not to dumb down this part down, but rather to use the familiar concept of the data type as a way of sneaking in the full flexibility and power of the object model.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea2dd63a19db" width="1" height="1" alt=""><hr><p><a href="https://medium.com/all-the-things/data-types-for-humans-ea2dd63a19db">Data types for humans</a> was originally published in <a href="https://medium.com/all-the-things">A Place For Everything</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A secret agent database for the rest of us]]></title>
            <link>https://medium.com/all-the-things/a-secret-agent-database-for-the-rest-of-us-de39abfb2a08?source=rss----f1c4348ac90d---4</link>
            <guid isPermaLink="false">https://medium.com/p/de39abfb2a08</guid>
            <category><![CDATA[design]]></category>
            <category><![CDATA[software]]></category>
            <category><![CDATA[ui]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Herb Caudill]]></dc:creator>
            <pubDate>Sat, 22 Dec 2018 16:43:03 GMT</pubDate>
            <atom:updated>2018-12-22T16:41:54.677Z</atom:updated>
            <content:encoded><![CDATA[<h4>Why shouldn’t our tools for organizing information be as cool as the ones in the movies?</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jrxpw3vsHQx7XjjRDBZiuw.png" /></figure><p>I have a long-standing love-hate relationship with the way computer interfaces are portrayed in films.</p><p>On the one hand, they’re so unrealistic! Lynne has patiently sat through any number of post-movie rants about this kind of thing:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*XNLlC7LKlxZVAeCx4lqGMw.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*4R2cJaZYjv4BlMn30lZVLA.gif" /></figure><p>On the other hand, they’re kind of awesome! At their best, film UIs capture the way we <em>wish </em>computers worked:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/535/0*jgOMevZ-deY9tv6J.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*tJ6kqhv0ieacUiGoWucz4Q.gif" /></figure><p>In the movies, no one ever spends five minutes looking for a file. In the movies, no one struggles to print something from their laptop.</p><p>In the movies, when you type someone’s name into the computer, you’re immediately presented with everything you need to know about them:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kZJBq15ke6bvrCJKpX3rbA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*OA2TQV2tq1BxbKpCmI_NvA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/777/1*auDTNgXZH1n-wRHR4jZkHw.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/620/1*-PEeinnSNlWcVa1AV_SRTQ.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SSnpTNu5UHNg7_Bso9RNZw.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*dMgO11mIbmZetB9Wp0FwdA.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*izI_o-sAyrgVWigdBQHcVA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*t510GzPVN1OQzP9XykbT6w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/555/1*lrje37x5BVTWpozSWwVOaA.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*mEf-TLOjkEKjsIHiUNFJ3g.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/436/1*Vliol4v-N_st1_AWQEl_xA.jpeg" /><figcaption>Mugshots, surveillance photos, maps, scans of paper records, ID cards, maps, fingerprints, 3D body scans: It’s all there, in one place, when you need it.</figcaption></figure><p>When I see the computer systems used by spy organizations in the movies, I always have the same three thoughts:</p><p><strong>Thought #1: </strong>Why do secret agents type so loudly?</p><p><strong>Thought #2: </strong>I’d love to just get a glimpse at the systems real-world spies use. Going by the Agency databases I <em>have </em>seen, I’m guessing they’re not all that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LRM7XjMqYghV48mN7YBUsw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8l9H_ZWUT2PXjhLj4rSH6w.png" /><figcaption>On the other hand, maybe the CIA saves all the good software contractors for their internally-facing systems — who knows!</figcaption></figure><p><strong>Thought #3: </strong>It sure would be nice if, at <em>my </em>job, I could type in, say, a customer’s name — and see everything I need to know about them, in once place.</p><p>So let’s take the secret agent use-case as a way of anchoring our thinking. <strong>Let’s focus on an imaginary user — let’s call her Celeste.</strong> Celeste has a desk job at the headquarters office of an intelligence organization. She’s smart. She’s not a programmer, and she doesn’t have a team of programmers at her disposal. It’s her job to put all the organization’s information about secret agents at her team’s fingertips.</p><p>Specifically, Celeste wants to be able to type in an agent’s name <em>(klackety-klackety-klack)</em> and see something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aoyXMzKHOk3MnHh6La2UfQ.png" /><figcaption>This shouldn&#39;t be rocket science, should it?</figcaption></figure><p>Now, this screen has no slowly rotating 3-D models, no live satellite tracking in grainy grayscale, no ANSI 381-encoded fingerprint templates. <strong>Just a bunch of facts. </strong>It doesn’t even need to make little futuristic beeps as it loads. This shouldn’t be hard, right?</p><p>Unfortunately, <strong>none of the tools we have today are of any use to Celeste.</strong></p><p>So how is she making do?</p><p>Perhaps <strong>she already has a spreadsheet </strong>with a row for each agent, with their names and vital stats. <strong>But it’s limited</strong>: There’s no place for photos or documents. And when she was trying to come up with a good way of capturing agents’ language capabilities — a table inside a cell? a couple of linked tables? numbered columns? — <a href="https://medium.com/all-the-things/a-single-infinitely-customizable-app-for-everything-else-9abed7c5b5e7#27b1">her brain melted</a>.</p><ul><li>She tried some <strong>database-spreadsheet hybrids</strong> like Airtable and Fieldbook, but they were <strong>not much better than spreadsheets</strong>: Neither one lets you customize a form like this; and neither one can handle things like the log of notes or the language skills table.</li><li>She looked at <strong>easy-to-use </strong>off-the-shelf systems for storing information about people— contact managers, CRMs, ERPs, etc. — but they weren’t made for the espionage business. With a little imagination, maybe you can stretch things a little bit — think of Agents as Customers, and Missions as Deals, and so on—but at some point you run into the limits of whatever customizability these systems offer.</li><li>And she probably considered highly <strong>customizable </strong>application builders — Mendix, Salesforce, Microsoft PowerApps, Google App Maker —but found them <strong>intimidating and unapproachable</strong>.</li></ul><p>So it’s up to us to design a system that could help Celeste. Her happiness is in our hands.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=de39abfb2a08" width="1" height="1" alt=""><hr><p><a href="https://medium.com/all-the-things/a-secret-agent-database-for-the-rest-of-us-de39abfb2a08">A secret agent database for the rest of us</a> was originally published in <a href="https://medium.com/all-the-things">A Place For Everything</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Solving the software puzzle for teams]]></title>
            <link>https://medium.com/all-the-things/the-trouble-with-saas-279694551b25?source=rss----f1c4348ac90d---4</link>
            <guid isPermaLink="false">https://medium.com/p/279694551b25</guid>
            <category><![CDATA[ideas]]></category>
            <category><![CDATA[saas-tools]]></category>
            <category><![CDATA[software]]></category>
            <dc:creator><![CDATA[Herb Caudill]]></dc:creator>
            <pubDate>Thu, 10 May 2018 13:45:31 GMT</pubDate>
            <atom:updated>2019-05-11T11:47:53.119Z</atom:updated>
            <content:encoded><![CDATA[<h4>Putting together a software toolset for a team is harder than it looks. Why is that?</h4><p><strong>I’m a huge fan of the software-as-a-service (SaaS) model</strong>, both as a software creator and as a software consumer.</p><p>It’s how I earn a living. My company makes <a href="http://www.devresults.com/">DevResults</a>, a SaaS product for a very niche market (monitoring and evaluation for foreign aid programs). Our software addresses a real need, more effectively and more cheaply than the in-house custom databases that used to be the only option.</p><p>And SaaS is how my team is able to collaborate closely even though we’re scattered all over the globe. We couldn’t do what we do without our toolset of web-based apps, which includes G Suite, QuickBooks Online, Zoom, Slack, GitHub, Asana, FreshDesk, FreshSales, Backblaze, Expensify, Headway, Knowledge Owl, and more.</p><p><strong>But our toolset still falls frustratingly short</strong>. We have too many different tools that, for the most part, don’t talk to each other. Every tool comes with its own implicit worldview, which you can either adopt or spend your life fighting against. And we have needs that aren’t met at all, because they’re too specific to our situation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vFTSIL9W7SJqpUKS-dlHJQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DHyPsI0mfDoDUL-u-4RxMg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LV3KKwIRh96g7t8615K5Kg.png" /><figcaption>So this is how we work. Lots of different tools that kind of fit our needs, but not really; and big areas where we just make do.</figcaption></figure><p>Large organizations solve these problems with big, expensive, heavily customized implementations of enterprise software suites. (At least I think they do! I’m not really sure how well that works in practice.)</p><p>But small businesses, little nonprofits, and teams within larger organizations know what I’m talking about. DevResults’ customers know what I’m talking about. <strong>We all have problems that should be solved by software, but aren’t.</strong></p><h3>The trouble with commercial software</h3><p>The problem has three parts to it:</p><ol><li>There’s lots of duplication and overlap between applications.</li><li>We’re paying for lots of stuff we don’t use.</li><li>There are still gaps that we have to cover with home-made systems.</li></ol><p>Let’s look at each of these in turn:</p><h4>1. There’s lots of duplication and overlap between applications.</h4><p>Sometimes it feels like my team uses a ridiculous number of products with overlapping footprints.</p><ul><li><strong>Customers </strong>are explicitly represented in our CRM system (FreshSales), in our accounting system (QuickBooks), and in our help desk system (FreshDesk).</li><li><strong>Teams </strong>are explicitly represented in our source control system (GitHub), our project management system (Asana), and our central identity management system (Azure AD).</li><li><strong>Tasks </strong>are explicitly represented in Asana, FreshSales, FreshDesk, and GitHub.</li><li><strong>Conversations</strong> between team members can happen in GitHub, in Slack, in FreshSales, or in Asana.</li><li><strong>Files </strong>might be found in Slack, Asana, FreshSales, FreshDesk, or Google Drive.</li></ul><p>This duplication is a problem: For one thing, we’re paying twice (or three or four times) for these features.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CMz-o605JpQjsqh0cjn-lA.png" /></figure><p>It’s not just the expense: Duplicate features add to everyone’s <strong>cognitive burden</strong>. It’s not clear where some things belong. Do software development tasks go in Asana, which is what we use for managing other projects and tasks? Or do they go in GitHub Issues, which is optimized for that purpose? Either way we have to make rules.</p><p>But the bigger issue is that we don’t have a <strong>single source of truth </strong>for lots of things. In many cases we just have to maintain multiple, parallel lists in different places, with all the repetitive work, human error, and lack of clarity that comes with that.</p><h4>2. We’re paying for lots of stuff that we don’t use.</h4><p>There are big areas of unused functionality in every tool that we just ignore. For example:</p><ul><li>FreshSales: Territories, lead scoring, in-app calling</li><li>Asana: Conversations, progress dashboards</li><li>FreshDesk: Forums</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qj_PKiA4re0ElOCF3UEc2A.png" /></figure><p>These are often great features in their own right, that a bunch of smart people spent a lot of time building. But for whatever reason, we don’t need them: Maybe we don’t have the problem they solve. Maybe we’ve solved those problems some other way. Either way we’re paying for something that we don’t use.</p><h4>3. There are still gaps that we have to cover with home-made systems.</h4><p>Finally, there are still important gaps — parts of our team model that aren’t represented by any of these tools.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iO3mnIrThKY4YbyKXFMcyw.png" /></figure><p>So for example we don’t have a definitive list of ongoing projects or teams anywhere.</p><p>There’s also a lot of vital information about customers and instances that we don’t have a good place for. We have one spreadsheet where we keep track of when it’s time to bill each customer for their annual subscription. We have another where we keep background information on individual projects using our software, for support purposes — notes about contacts, how they’re using specific features, etc.</p><p>I could go on. We have a spreadsheet for cash planning and another to keep track of debts and payments. In order to get an overview of the company’s financial health going forward, I’ve cobbled something together that pulls numbers from QuickBooks, FreshSales, and from other spreadsheets. This gives me a janky dashboard that is currently broken because a Zapier task stopped running for a couple of months.</p><h4>So that’s where we are. It’s a bit of a mess.</h4><p>We’ve pieced together a bunch of apps to try and capture all of our work, and it’s simultaneously <strong>too much</strong> and<strong> not enough</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tdua45fBEeTGf78KLMb_wg.png" /></figure><p>It’s a lot for a new employee to figure out: I recently spent a couple of days just documenting on our wiki how everything fits together. Even people who have been here a long time aren’t always sure where to start a conversation, where to share a document, or where to record an important fact about a customer.</p><h4>Systems fatigue is real.</h4><p>Any time anyone suggests a new tool to the team (usually me!) there’s an audible sigh — even if it covers a gap we know we have. Really? Another system to figure out, with its own username and password and its own UI quirks and its own place to upload your avatar? Another system to provision or deprovision when people come and go?</p><p>The SaaS ecosystem is amazing — if you think of a problem, you can almost certainly find a product out there that addresses it, for $1 or $25 or $100 per user per month. But realistically you can only have so many different systems. Not just because those subscription costs start adding up, but also because the indirect costs — the staff time required to administer them and the cognitive burden of learning and thinking about them — start adding up.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rCuUAsNcs33HvQSH8JbzSw.png" /><figcaption>iDoneThis does one thing well. But is it worth $1200/year, plus the cognitive and administrative burden of having Yet Another System?</figcaption></figure><p>Consider the case of <a href="http://www.idonethis.com/">iDoneThis</a>, a little single-purpose service that provides each team member a place to say what they got done every day.</p><p>It’s such a simple thing, but makes our weekly meetings a lot shorter because we don’t have to go around asking people what they’ve done. So it provides real value to our team! But it’s a perennial candidate for the chopping block. Our plan costs $9 per user per month, so about $1,200 per year — not a lot of money, but not nothing either. And it’s such a trivial app — couldn’t it be replaced with just a Slack channel? Or a spreadsheet? Or a Google Doc?</p><h4>We’re part of the problem too!</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TcudaQWJiuXQna1av6eEsg.png" /></figure><p>Our customers’ experience with our SaaS product gives another perspective on the same set of problems. I see our customers struggling with DevResults in the same ways we struggle with our toolset:</p><ul><li>Trying to figure out what in their organizational model maps to a DevResults “activity”.</li><li>Using tags to make up for the lack of custom fields.</li><li>Ignoring entire sections of the app because they solve problems that don’t have, or that they’ve solved elsewhere.</li><li>Persuading users to learn to use yet another system.</li><li>Wondering how DevResults fits in with other tools they use: When it has duplicate functionality, which to use? How to sync up with information that lives elsewhere?</li></ul><h3>Two trade-offs and two failed promises</h3><p>Let’s take a look at two big trade-offs you face when putting together a software toolkit for a team:</p><h4><strong>Trade-off 1: Minimizing the number of systems vs. using the best tool for the job</strong>.</h4><p>If you go the first route and try to use as few systems as possible, you end up with sprawling multi-purpose software that does a half-ass job at lots of things.</p><p>If you go the second route and try to use the best system for each need, you end up with a hive of unconnected single-purpose tools.</p><p><strong>In theory</strong>, this trade-off can be avoided by <strong>integration</strong>. If your systems talk to each other, it’s not as much of a problem to have lots of focused single-task apps. Along the same lines, if you can get <strong>single sign-on </strong>working, people don’t have to keep track of lots of different passwords; and if you can get <strong>automated provisioning and deprovisioning</strong> working, then you don’t have to maintain duplicative lists of users in each system.</p><p><strong>In reality</strong>, integration turns out to be too hard to get working for most small teams and organizations.</p><h4>The failed promise of integration</h4><p>Some systems have built-in integrations. Slack, for example, advertises hundreds of integrations. In practice, this mostly just means that you can send an app’s notifications to Slack channels.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*hGJOrxpZtIelPA6RHwo-Dw.gif" /><figcaption>Slack “integration” with Google Drive: 🙄 Meh.</figcaption></figure><p>Other built-in integrations turn out to be pretty lame when examined closely. Asana and Slack both “integrate” with file storage services like Dropbox and Google Drive. In practice, this just means that Asana and Slack both have a clumsy built-in file picker for sharing files directly from your cloud storage. It’s easier to just drag and drop files from your local file system.</p><p>Often it’s attractive to buy into a suite, or use multiple products from the same vendor, precisely because they promise to fit better together. That was the deciding factor for us in adopting FreshSales as a CRM — we were already using FreshDesk for support tickets, and we hoped to smoothly consolidate contact data and interaction history from before the sale (CRM) and after the sale (help desk). In practice, this was nearly as awkward as if we were working with completely unrelated products.</p><p>Zapier lets you link one app to another via their public APIs: A trigger in one app results in an action in another. In some simple cases it works great. But the trigger/action model can be clumsy and brittle in practice. Getting two systems to maintain the same dataset in sync is a real challenge. Sometimes it just fails silently, and you don’t realize it until you go to look at your home-made dashboard and it doesn’t have any data for the last couple of months.</p><h4><strong>Trade-off 2: Using generic off-the-shelf tools</strong> vs. <strong>creating custom solutions</strong>.</h4><p>Commercially available software gives you a high level of quality for your money, but you have to make do with a model that only approximates your world.</p><p>A custom solution is usually going to be a little lame: In most cases, these solutions are just spreadsheets; but even if you have an in-house development team you’re not likely to match the level of polish and robustness of a commercial product.</p><p><strong>In theory</strong>, this trade-off can be avoided by <strong>customizability</strong>. If you can use a well-tested, off-the-shelf system, but then configure it to meet your needs, you have the best of both worlds.</p><p><strong>In reality, </strong>customizability never goes far enough.</p><h4>The failed promise of customizability</h4><p>In some cases we can add custom fields to capture our needs, and maybe change the order of fields on a form. But we always very quickly bump into the limitations of customizability.</p><ul><li>We’ve added a bunch of custom fields to FreshSales, but different people are interested in different things. The biznass tribe is interested in pre-sale information; the support tribe is interested in fields that won’t be populated until post-sale. There’s no way to show different forms to different teams.</li><li>Asana has per-project custom fields, which we sometimes use to assign time estimates and priority levels. But there’s no way to get a global view of these; and they don’t show up at all in individual task lists.</li></ul><h3>Deconstructing the typical SaaS app</h3><p>What would a solution to this problem look like? Let’s start by thinking about how the value of a typical SaaS application breaks down:</p><ul><li>The most valuable part of an application is its <strong>superpowers</strong> — unique features that would be difficult to replicate.</li><li>The <strong>core value </strong>of the application lies in its <strong>domain-specific data model</strong> combined with <strong>superpowers</strong>.</li><li>A layer of <strong>infrastructure </strong>has to be there for things like managing users and authenticating them.</li><li><strong>Me-too features </strong>are tacked onto the app in an attempt to turn it into a one-stop shop.</li><li><strong>Customizability </strong>and <strong>integration </strong>are weakly-implemented afterthoughts.</li></ul><h4>1. A domain-specific data model</h4><p>This is where an app captures its understanding of a domain.</p><ul><li><strong>Quickbooks </strong>models a company’s <strong>finances</strong>: customers, vendors, transactions, accounts.</li><li><strong>FreshSales </strong>models a team’s <strong>sales </strong>process: reps, leads, deals, accounts.</li><li><strong>FreshDesk </strong>models a team’s <strong>support </strong>process: agents, customers, tickets.</li><li><strong>DevResults </strong>models an <strong>international development</strong> program: activities, indicators, results frameworks, locations.</li><li><strong>Asana </strong>models an organization’s <strong>work</strong>: people, teams, tasks, projects.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QQ2u8N4S2820mDgOek9oWQ.gif" /><figcaption>FreshSales has a data model that captures a “typical” real-world sales process. A <strong>deal</strong> (shown here) has a name, a dollar amount, an expected close date, and a probability. It lives in one <strong>stage </strong>of several that I can define. It’s associated with one <strong>account</strong>, one <strong>sales rep</strong>, and one or more <strong>contacts</strong>. This model may or may not exactly match the way we work; either way it’s not rocket science.</figcaption></figure><p>There’s nothing technically difficult about this part: These are just database tables in a relational schema, plus a web UI for browsing and editing records. The web UI piece is not totally trivial, and some do it better than others. Still, it’s a problem that’s been solved thousands of times over.</p><p>Some products understand their customers better than others, so they have models that feel more natural. So this can be a differentiator, especially if the product is aimed at a niche market.</p><p>Still, even if a software company really understands its domain, the data model still just maps to an ideal or generic domain, and every team and every organization is different. So even at best, the data model only approximately matches your real-world needs.</p><h4>2. Superpowers</h4><p>These are the non-trivial skills that set the app apart.</p><ul><li>For GitHub, it’s the git hosting plus UI and workflow around the repositories.</li><li>For Expensify, it’s parsing invoices and receipts.</li><li>For FreshDesk, it’s allowing multiple people to collaborate on triaging a support email inbox.</li><li>For Trello, it’s a well-implemented and unique user interface. (Was unique, anyway! It’s been copied everywhere from Asana to GitHub.)</li><li>For Dropbox and Google Drive, it’s document storage and syncing.</li><li>For Slack, it’s team chat.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q1qIyPEkuoUL5oP8VpWJOA.gif" /><figcaption>Expensify‘s superpower is called “SmartScan”. Drag and drop an invoice or a receipt, and it magically pulls out the vendor, date, and amount. I don’t know if they do it with people or with machines, but either way it would be pretty hard to reproduce this feature!</figcaption></figure><p>Not every app has a superpower! Some apps’ value is all in the way they’ve successfully modeled their domain’s data.</p><h4>3. Me-too features</h4><p>These are things that go outside of an app’s core strengths.</p><ul><li>Github, FreshSales, and FreshDesk (and DevResults) have some form of task lists.</li><li>GitHub and Asana (and DevResults) have discussions.</li><li>Slack, Asana, FreshSales, and FreshDesk (and DevResults) have document storage.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2po7WQb7Iy-ZRV2BrfjrRQ.jpeg" /><figcaption>Asana’s “Conversations” is a classic me-too feature: In principle it kind of makes sense to have discussions alongside your task lists. In practice, it doesn’t make sense to have lots of different venues for conversation, so we use Slack instead.</figcaption></figure><p>Their only advantage is that they’re linked to the app’s data model. They often weren’t part of the original product idea, but it’s very appealing to have that set of documents or that to-do list right next to the project or help ticket or whatever it is that it pertains to. That’s why customers asked for them, and that’s why they got built. But they’re almost always built in a half-hearted way, and there are always single-purpose tools that are better at these things.</p><h4>4. Infrastructure</h4><p>These are the things that every app has to have: User management, provisioning, authentication, permissions, an API and API documentation, subscription management and billing, search.</p><p>Again, some products nail the fundamentals better than others, but these are all problems that have been solved many times.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DJEyElt_x36V98nKQhh9WQ.png" /><figcaption>N apps = N user lists to maintain.</figcaption></figure><p>If you use ten SaaS apps, you have ten different lists of users to maintain; ten APIs to learn about; ten places to update your credit card information when the expiration date changes; ten different search UIs.</p><p>And you’re paying indirectly, ten times, for the cost of building and maintaining all this parallel infrastructure.</p><h4>5. Customizability and integration</h4><p>As we’ve already seen, if we get these at all, they’re implemented in a half-hearted and limiting way.</p><h3>The holy grail</h3><p>This framework gives us a way of thinking about what we’re getting from various apps in our toolset, and what an ideal arrangement would look like.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Lee0d9ylJi56lp9AOIFanw.png" /></figure><p>This is what I want. This turns the above model on its head:</p><ul><li><strong>Customization </strong>and <strong>integration </strong>come first<strong>.</strong></li><li><strong>Domain-specific data models</strong> are free, pluggable, and customizable.</li><li>Common features are shared and linked, so there’s no need for duplicative <strong>me-too features</strong>.</li><li><strong>Superpowers</strong> are left to integrated apps — or better yet to infrastructure-free plugins<strong>.</strong></li></ul><p>I’ve thought a lot about what this application might look like. I’ll go into the details in a follow-up post.</p><p>I’ve also spent some time scouring the landscape for an existing app that fits the bill. (Spoiler: I haven’t found one.) In another post, I’ll take a look at some products in existing software categories that come close.</p><h4>Next:</h4><p><a href="https://medium.com/all-the-things/a-single-infinitely-customizable-app-for-everything-else-9abed7c5b5e7">Excel Hell: A cautionary tale</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=279694551b25" width="1" height="1" alt=""><hr><p><a href="https://medium.com/all-the-things/the-trouble-with-saas-279694551b25">Solving the software puzzle for teams</a> was originally published in <a href="https://medium.com/all-the-things">A Place For Everything</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Excel Hell: A cautionary tale]]></title>
            <link>https://medium.com/all-the-things/a-single-infinitely-customizable-app-for-everything-else-9abed7c5b5e7?source=rss----f1c4348ac90d---4</link>
            <guid isPermaLink="false">https://medium.com/p/9abed7c5b5e7</guid>
            <dc:creator><![CDATA[Herb Caudill]]></dc:creator>
            <pubDate>Wed, 09 May 2018 09:52:07 GMT</pubDate>
            <atom:updated>2018-12-19T08:26:07.812Z</atom:updated>
            <content:encoded><![CDATA[<h4>Before we create a “single infinitely-customizable app for everything else”, we need to understand why we’re currently using spreadsheets for everything.</h4><blockquote>Previous article: <a href="https://medium.com/all-the-things/the-trouble-with-saas-279694551b25">Solving the SaaS puzzle for teams</a></blockquote><p>After writing <a href="https://medium.com/@herbcaudill/the-trouble-with-saas-279694551b25">many words about the challenges of building a SaaS toolset for a team</a>, I concluded that the holy grail would look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Lee0d9ylJi56lp9AOIFanw.png" /></figure><p>I’d mentioned to some people on my team that I was thinking over a new product idea. When I published that post, this was the first reaction:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/925/1*0OaQA7Z_ix38OSCOXowm8A.png" /></figure><p>The conventional wisdom in the software business is to <strong>do one thing well.</strong> In general that’s good advice, and that thinking has brought us some terrific, tightly-focused tools.</p><p>So it seems a little crazy to talk about building a multipurpose SaaS product in this day and age.</p><p>On the other hand, there’s clearly enormous demand for an extremely malleable general-purpose application. Exhibit A for that demand is Microsoft Excel.</p><h3>The curious case of Microsoft Excel</h3><p>The reality of most teams, including mine, is this: We use purpose-built software for some stuff, and for the rest we use Excel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iO3mnIrThKY4YbyKXFMcyw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cRFVHw3W-NWgGrKobKN3ag.png" /></figure><p>In most cases, Excel is not up to the demands we put on it — but that doesn’t stop us. There are other tools out there that might be more appropriate, but we don’t use them.</p><p><strong>Excel sucks and people love Excel. </strong>I think this is important. If we want to create something to replace Excel in that “stuff we need” white space, then we need to understand why Excel is so appealing in spite of its limitations.</p><p>Let’s take a little detour to think about this.</p><h4>Excel is the world’s most widely-used “database”</h4><p>It goes without saying that Microsoft Excel is the world’s most-used <strong>spreadsheet </strong>software. What is surprising, and of interest to us, is that Microsoft Excel is the world’s most used <strong>database </strong>software as well.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fvc7jScClAFghBeOUmGfrQ.png" /><figcaption>VisiCalc was the original <a href="https://en.wikipedia.org/wiki/Killer_application">killer app</a> — software compelling enough to sell the hardware it ran on.</figcaption></figure><p>This isn’t what spreadsheets were made to do. This software category was introduced on the Apple II by VisiCalc — short for “Visible Calculator” — to do financial modeling. Bob Frankston, one of its creators, described it as “a magic sheet of paper that can perform calculations and recalculations”.</p><p>VisiCalc was supplanted by SuperCalc, then Lotus 1–2–3, and then Microsoft Excel. Each new generation advertised faster calculations, more functions, new “what-if” features. But in the early 1990s, Microsoft came to understand that most people didn’t use Excel for numerical computation: They used it for storage.</p><p>Joel Spolsky, who worked on the Excel team at the time, <a href="https://www.joelonsoftware.com/2012/01/06/how-trello-is-different/">describes the moment</a> when the team realized this:</p><blockquote>Everybody thought of Excel as a financial modeling application. It was used for creating calculation models with formulas and stuff. … Roundabout 1993 a couple of us went on customer visits to see how people were using Excel. Over the next two weeks we visited dozens of Excel customers, and <strong>did not see anyone using Excel to actually perform what you would call “calculations.”</strong> Almost all of them were using Excel because it was a convenient way to create a table. Spreadsheets are not just tools for doing “what-if” analysis. They provide a specific data structure: a table. Most Excel users never enter a formula. They use Excel when they need a table. <strong>The gridlines are the most important feature of Excel</strong>, not recalc.</blockquote><p>Realizing this, Microsoft belatedly started to add data-management features to Excel. The biggest single improvement was a feature tried in 2003 called, aptly, “Tables”. I suspect that 95% of Excel users don’t know it’s there. Microsoft is still retrofitting Excel with database functionality. Deep in the bowels of the “Data” tab you’ll find hardcore data manipulation tools that approximately no one has ever used.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vs9R3aQnpVfdkTWNmnbjdg.png" /><figcaption>Did you know you could create queries within an Excel spreadsheet to filter, project, transform, pivot or unpivot data from an existing table?</figcaption></figure><p>In spite of all that effort and all of the data-centric features that have been piled onto the product, it’s still an exercise in frustration to use Excel to manage data. Why is that?</p><h4><strong>Trapped in flatland</strong></h4><p>Excel’s most obvious limitation has to do with its very nature. There’s no getting around the metaphor of sheets of paper: a two-dimensional grid of rows and columns.</p><p>I believe there’s a moment in every Excel user’s learning curve when they realize that <em>their information doesn’t fit in two dimensions</em>, and their brain melts.</p><p>My wife, Lynne, is the prototype of the intelligent non-programmer. (She is also <em>stunningly beautiful</em> and very likely to read this.) I mentioned this idea to her, and she immediately knew what I meant. She told me about trying to use Excel to keep track of a training program across multiple hospitals: Venues, classes, syllabi, instructors, topics, participants. She got to a certain point, stared at the screen for a while, and said “Fuck it, I’m printing stuff out and buying a binder.” Paper 1, Excel 0.</p><p>Search Quora or StackOverflow and you’ll find dozens of variations of the question “<a href="https://stackoverflow.com/questions/32060804/is-it-possible-to-store-multiple-values-in-an-excel-cell">Is it possible to store multiple values in an Excel cell?</a>” or “<a href="https://stackoverflow.com/questions/2852755/insert-table-into-cell-excel">Can I insert a table into an Excel cell?</a>”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/970/1*D2koZntZElhSOH8rL_QKpw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/0*AdEtt_-nOeLmxOAh.gif" /><figcaption>Listen closely, and you can hear the sound of someone’s brain about to melt.</figcaption></figure><p>If you have a certain type of background, you look at a situation like this and you immediately see <strong>one-to-many </strong>or <strong>many-to-many </strong>relationships, and you know the solution involves multiple tables, which you can link together VLOOKUP or INDEX/MATCH.</p><p>But <em>most people’s</em> brains don’t go naturally to a relational-tables model, and <em>most people</em> have never used those functions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sqy2iq8MybBSGixKcV4WMQ.png" /><figcaption>Two signs that you’ve outgrown Excel: (1) Repeating the same data over, and (2) a series of numbered columns.</figcaption></figure><p>So in the best-case scenario, you get around Excel’s two-dimensionality with multiple tables and VLOOKUP hacks. More likely, you limp along with error-prone, labor-intensive horror shows like the one shown above — with information duplicated all over the place, and maybe with repetitive sets of numbered columns to spice things up.</p><h4>No separation between data and presentation</h4><p>Even if you’ve organized your data into strictly normalized relational tables, you’re very limited in terms of your ability to <em>view the data</em>.</p><p>That’s because in Excel, <strong>data and presentation are hopelessly commingled</strong>. This leads to irreconcilable tensions between two worthy goals — keeping the data clean on the one hand, and presenting it in a way that is readable and clear on the other.</p><p>So people are naturally tempted to do things like this — manually grouping and subtotaling different categories, with blank rows and brightly colored labels:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-dgXuTPTQGXMQNrvgVa5UA.png" /><figcaption>This is an official template provided by the Microsoft Corporation. It’s pretty, but of all people, they should know better than to offer this up as a good practice!</figcaption></figure><p>If you know spreadsheets, you know that this spreadsheet would be a nightmare to work with. You can’t get numbers into it or out of it without repetitive copying or pasting, or retyping. And it’s brittle — look at it wrong and you’ll break something. Just picture in your head what’s involved if you want to add a new expense category, or remove an existing one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1005/1*RJ3X49HrZdbx7FUqBjU7Gw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UXziB1V3_kiad5aFc6LvGw.png" /><figcaption>Even if you’ve done everything right, you can’t create these kinds of views on your data in Excel.</figcaption></figure><p>There’s no way to create a natural view of data with one-to-many or many-to-many relationships — for example a form or report like these, that draws from tables of data that are stored elsewhere.</p><h4>Error-prone</h4><p>The downside of Excel’s flexibility is that it’s really, really easy to do the wrong thing. You can enter text into a numeric column. You can type something that looks like a date but isn’t.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/981/1*Alqve5M5YApvJXEoQGAqMg.png" /><figcaption>Some of these were entered as M/D/Y and some as D/M/Y. Can you tell which is which?</figcaption></figure><p>You can inadvertently delete rows, type over a formula, omit cells from a calculation, or worse. Mistakes in Excel formulas are easy to make, hard to notice, and hard to debug.</p><p>As a result, the internet is littered with Excel horror stories, from the <a href="https://www.heraldextra.com/error-causes-million-shortfall-in-education-budget/article_ef3a6db0-8400-11e1-99e9-001a4bcf887a.html">$25-million budget shortfall</a> to the <a href="http://www.businessinsider.com/excel-partly-to-blame-for-trading-loss-2013-2">$6-<em>billion </em>trading loss</a> to the <a href="https://www.nytimes.com/2013/04/19/opinion/krugman-the-excel-depression.html">incorrect policy advice that deepened a recession</a>.</p><h4>File-based</h4><p>Twelve years after Google Sheets was introduced, and ten years after Microsoft launched Excel Online, most spreadsheet “systems” are still files that live on a server or on someone’s hard drive somewhere.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/0*_38KqRfN-vzJ4HT5.png" /></figure><p>As a result:</p><ul><li>Only one person can use them at a time.</li><li>They get emailed around, which is insecure.</li><li>You end up with multiple versions with cryptic names like “<em>Budget v3 (FINAL) modified HC Feb2018 (clean).xlsx</em>”.</li></ul><h3>So why don’t people use database software instead?</h3><p>So: We’re agreed that Excel isn’t a great tool for managing a database. If you Google “<a href="https://www.google.com/search?q=why+do+people+use+excel+as+a+database">Why do people use Excel as a database</a>” you’ll turn up page after page of rants, making the not-unreasonable two-part argument that:</p><ol><li>Excel is not database software; and</li><li>If you need a database you should use database software.</li></ol><p>So let’s talk about <strong>Microsoft Access</strong>, which <em>is</em> database software, and which doesn’t have these limitations.</p><ul><li>Access is designed around a SQL-like structure of relational tables with normalized data.</li><li>Its columns are typed, so you can’t enter a non-date in a date column or text in a numeric column.</li><li>Presentation and data are cleanly separated, so you can create various views, forms, and reports on top of the underlying data.</li></ul><p><strong>So why don’t more people use Access?</strong> It’s not because they’re too dumb to figure it out. My team includes four data scientists and five programmers. We can all write SQL in our sleep. We use spreadsheets <em>a lot</em> and we don’t use Access <em>at all</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S5Ra3-Opvn6PZMozaCMRpg.png" /></figure><p>The reason, I think, is best summarized in Venkatesh Rao’s answer on Quora to the question “<a href="https://www.quora.com/Why-do-many-people-still-use-Excel-as-a-pseudo-database">Why do many people still use Excel as a pseudo database?</a>”:</p><blockquote>One is a workspace for humans, the other is a store for programmatic manipulation.</blockquote><p><strong>Excel greets you with an empty, non-judgmental grid</strong>: You can start typing and very quickly get some degree of clarity and organization. Datasets in a spreadsheet are malleable, allowing you to be experimental and incremental.</p><p><strong>Access, on the other hand, forces you to do a certain amount of upfront thinking about the structure of your data</strong>. Once you’ve defined that structure and started filling it up with data, it gets harder and harder to make changes to it.</p><h3>Excel is no good for data. Access is no good for humans.</h3><p>This tension between spreadsheets and databases gives us a starting point for what A Single Infinitely-Customizable Tool For Everything Else might look like.</p><p>It has to let me easily create lists and tables like Excel. But in the long run it has to give my data a more solid structure like Access does.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QDBoBf5Bs8tmj4qrDBxHIg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eT_cGSvdFOmb0W0qibufDw.png" /><figcaption>Fieldbook (left) and Airtable both advertise the simplicity of spreadsheets combined with the power of a database.</figcaption></figure><p>As a product idea, “Spreadsheet meets Database” is not new. <a href="http://www.airtable.com/">Airtable</a> (“<em>Looks like a spreadsheet, acts like a database</em>”) and <a href="http://www.fieldbook.com/">Fieldbook</a> (“<em>As simple as a spreadsheet, as powerful as a database</em>”) beat me to it. They’re good products, and a step in the right direction. And there are dozens more products in this vein.</p><p>But I think we can go a step farther than just mashing up two existing product categories.</p><p>There are a couple of problems that the current crop of SpreadDataSheetBase products inherit from the database side of the family.</p><p>One problem with databases is that <strong>they force me to start naming and defining things before I have data.</strong></p><p>I want to just start typing in stuff without defining my data model in advance, and then start naming and defining things <em>as a schema emerges from the data</em>.</p><p>When that critical moment arrives and I realize that my data doesn’t fit in a grid, my brain shouldn’t melt; in fact it should be natural and obvious how to capture that complexity.</p><p>Which brings me to the larger issue with today’s user-facing database products, which is that <strong>they force me to cram my reality into linked two-dimensional tables</strong>.</p><p>The relational data model is wonderful; but it was invented to make things easy for machines, not for humans. And if you spend any time talking to intelligent civilians about their struggles using Excel as a database, you’ll realize that it’s not a model that occurs naturally to many people.</p><h4>Thinking outside the grid</h4><p>I have a theory that <strong>a loosely-typed object-oriented model</strong>, like JavaScript objects or JSON documents in a NoSQL database, is <strong>a more natural fit for most people’s mental models </strong>than a strictly-typed relational table structure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lOivo4SeIXZMtplwYdmLKw.jpeg" /><figcaption>Photo © 2011 by Simon Bramwell</figcaption></figure><p>I was recently talking with a DevResults customer who was managing data from an educational project that works in several developing countries. Since this scenario is fresh on my mind, let’s consider a simple data model for a school.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/613/1*jVAqZZL5d8mqgfSDHylLSw.png" /></figure><p>In this model, a <strong>school </strong>“has”<em> </em>a <strong>name</strong>, a <strong>region</strong>, a <strong>type</strong>, and a list of <strong>classes</strong>. It’s not unreasonable to think of each of these things as a <em>field </em>or a <em>property</em> of a school, even though one of them is a list.</p><p>Likewise, a <strong>class </strong>“has” a <strong>grade</strong>, a <strong>section</strong>, a <strong>teacher</strong>, and a list of <strong>students</strong>.</p><p>We can capture this information in a JavaScript object or a JSON file in a succinct way that aligns nicely with our mental model.</p><pre>{<br>  name: &#39;<strong>EP de Mali-Ville</strong>&#39;,<br>  region: &#39;<strong>Labé</strong>&#39;,<br>  type: &#39;<strong>Public</strong>&#39;,<br>  classes: [<br>    {<br>      grade: <strong>3</strong>,<br>      section: &#39;<strong>A</strong>&#39;,<br>      teacher: { first: &#39;<strong>Ousmane</strong>&#39;, last: &#39;<strong>Kamara</strong>&#39;},<br>      students: [<br>        { first: &#39;<strong>Amadou</strong>&#39;, last: &#39;<strong>Diallo</strong>&#39;},<br>        { first: &#39;<strong>Ouri</strong>&#39;, last: &#39;<strong>Sow</strong>&#39;},<br>        { first: &#39;<strong>Jariatou</strong>&#39;, last: &#39;<strong>Balde</strong>&#39;},<br>        { first: &#39;<strong>Mariama</strong>&#39;, last: &#39;<strong>Ba</strong>&#39;},<br>      ]<br>    },<br>    {<br>      grade: <strong>3</strong>,<br>      section: &#39;<strong>B</strong>&#39;,<br>      teacher: { first: &#39;<strong>Mamadou Aliou</strong>&#39;, last: &#39;<strong>Diallo</strong>&#39;},<br>      students: [<br>        { first: &#39;<strong>Mamadou</strong>&#39;, last: &#39;<strong>Balde</strong>&#39;},<br>        { first: &#39;<strong>Kadiatou</strong>&#39;, last: &#39;<strong>Diallo</strong>&#39;},<br>      ]<br>    },<br>  ]<br>}</pre><p>In contrast, the relational model of the same data introduces a weird layer of abstraction and indirection.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1015/1*T-bojAiwLBaC3ZjwQQMZFw.png" /></figure><p>This is weird for a couple of reasons.</p><p>Even though a <strong>class </strong>has no free-standing existence outside of a <strong>school</strong>, we create a table that only contains <strong>classes</strong>. It’s weird that if you look at a <strong>school</strong>, you’ll find no mention of its <strong>classes</strong>; and if you look at a <strong>class</strong>, you’ll find no mention of its <strong>students</strong>. Rather, the <strong>class </strong>is stored as a property of a <strong>student</strong>, and the <strong>school </strong>is stored as a property of a <strong>class</strong>.</p><p>Now, this abstraction makes total sense once you understand it. But it’s unnatural. It maps awkwardly to my internal representation of reality. (In related news, relational data also <a href="https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch">maps awkwardly to the object-oriented code</a> it has to be translated to.) This awkwardness creates a set of <strong>cognitive obstacles</strong> that most people don’t make it past.</p><p>If my “easy-to-use database” requires me to conceive and implement this non-intuitive structure <em>before I can enter the information for a single school</em>, I may very well conclude that I’m better off with paper and a binder.</p><p><strong>One cognitive obstacle is the tabular model</strong>, which locks us into rows and columns, with each cell containing a primitive value: a number, or a date, or a string of text.</p><p>An object model frees us up to think about things in a recursive, hierarchical way. As it turns out, <a href="https://www.amazon.com/Recursive-Mind-Origins-Language-Civilization-ebook/dp/B00IRL3L7I">this is how the human brain is wired to represent reality</a>. Things are composed of other things, which are in turn composed of other things. A property of a JavaScript object can contain a primitive value, but it can also contain a complex object with its own properties. Or it can contain an array, which in turn can consist of simple values or complex objects.</p><p>Now, this is not to say that tables are passé as a way of viewing and organizing information. There’s a reason why Excel’s gridlines are so compelling. A table is a clear and concise way of displaying a set of facts (columns) about a collection of similar objects (rows). But it’s not the only way! There are lots of other ways to visualize a collection — an outline, a network graph, a timeline, a map, a kanban board. Most database UIs I’ve seen begin and end with tables, and I suspect that’s in large part the underlying tabular storage technology showing through.</p><p><strong>Another cognitive obstacle is the strong typing requirement</strong>: In a relational database, you can’t enter data before you’ve decided what <em>type </em>of data each field of each table will contain. That’s as true of SQL Server as it is of Airtable.</p><p>In loosely-typed environments like JavaScript and NoSQL databases, <em>I can</em> create a schema but <em>I don’t have to. </em>That gives me the flexibility to just enter my stuff without thinking about it too much at first — like in Excel — and allow the schema to emerge later.</p><p>Because it is helpful to have a schema at some point: If the software knows that a field should only contain dates, it can reject non-date input; it can offer me a helpful UI like a date-picker; and it can offer date-based views like a calendar, a timeline, or a timeseries chart.</p><p>But the schema should know its place. Its job is to serve, not to run the show. It shouldn’t insist on coming first. I should be able to modify it as needed. It should observe quietly, inferring types where possible from the data (much the way Excel recognizes dates and currency amounts when they’re typed in, and applies the corresponding formatting) and only asking for clarification when something <em>I </em>want to do requires it.</p><p>To be clear, <strong>the notion of relations <em>isn’t </em>a cognitive obstacle</strong>. In the JSON snippet above, I’ve embedded the teachers’ information directly in the class. In this scenario, perhaps it’s a good idea to store a list of <strong>teachers </strong>separately, and just store references to them; that way if we misspell a teacher’s name we only have to fix it in one place. I’m guessing that our intelligent civilian can probably handle that just fine, and transition easily from the model described above to something like this:</p><pre>{<br>  name: &#39;<strong>EP de Mali-Ville</strong>&#39;,<br>  region: &#39;<strong>Labé</strong>&#39;,<br>  type: &#39;<strong>Public</strong>&#39;,<br>  classes: [<br>    {<br>      grade: <strong>3</strong>,<br>      section: &#39;<strong>A</strong>&#39;,<br>      teacher: <strong>101</strong>,<br>      students: [ <strong>1</strong>, <strong>2</strong>, <strong>3</strong>, <strong>4</strong> ],<br>    },<br>    {<br>      grade: <strong>3</strong>,<br>      section: &#39;<strong>B</strong>&#39;,<br>      teacher: <strong>102</strong>,<br>      students: [ <strong>5, 6</strong> ]<br>    },<br>  ]<br>}</pre><p>The difference is that a relational database <em>forces </em>you to put everything in a remote table, even where it doesn’t make sense — so <strong>classes </strong><em>have </em>to be stored separately from <strong>schools</strong>. In an object model, you have the choice.</p><p>What would be even better would be if the software treated that as an internal implementation detail, and spared me from having to think too much about whether an object is stored in embedded form or as a reference.</p><p>OK. So our product description is starting to take a <em>little </em>more shape:</p><ul><li>It gives me an <strong>easy entry point</strong> like Excel, and some relational power like Access.</li><li>It <strong>doesn’t require me to give things a lot of definition up front</strong>, but it allows me to do so when necessary.</li><li>Instead of exposing an underlying structure of tables and rows, it <strong>exposes an underlying structure of arrays and objects</strong>.</li><li>I can see my information in <strong>tabular form,</strong> but I also have access to <strong>lots of other visual representations</strong> that might make sense for my data.</li><li>And of course, it’s all <strong>delivered in the browser</strong>, so it supports real time collaboration; and the app and the data live in the cloud, so it’s highly available and scalable and all that.</li></ul><p>This is probably still a little hard to picture. I have many thoughts about what the user interface would look like, but I’ll stop now and share some sketches and mockups in a subsequent post.</p><p>I also have many thoughts about how to do integration right; that will probably need to be its own post as well. And I’ve promised to share what I’ve found while searching for an existing product like this.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9abed7c5b5e7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/all-the-things/a-single-infinitely-customizable-app-for-everything-else-9abed7c5b5e7">Excel Hell: A cautionary tale</a> was originally published in <a href="https://medium.com/all-the-things">A Place For Everything</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>