Solution Evaluation: Databases

- 4 minutes read - 803 words

Databases

Scoring

Let’s apply the same rating standard:

  1. Permit cycles (1)
  2. Permit calculated attributes (1)
  3. Changeset support (0)
  4. Schema changes are easy and cascade easily (1)
  5. Self-Documenting (0)
  6. Data can be delivered natively in a variety of views (0)
  7. Data is readily accessible to non-technical staff (1)
  8. Data is readily updated by non-technical staff (1)
  9. Price (3)

Total: 8

This ranking surprised me! “Since a database is a more powerful application than a spreadsheet, surely it should score higher,” went my thinking. While clean data might be compelling enough to opt for a database, its interfaces are poor and less-accessible which hurts it badly in our criteria. Interestingly, this problem does not get better by buying more expensive databases: the interface tools still trend toward the primitive.

Its UI/UX (even with third-party tools) remains an insurmountable hurdle for the diverse consumers we’ve already identified. In the collapsed section below, I provide an example that demonstrates this problem.

Extended demonstration of difficulties with UI when building an accounting in a database.

We’ll use the Stooges’ TodoList example again. Setting up the basic equivalents of “sheets” is a far more difficult affair that requires specialized knowledge.

To create the tables we must:

  1. Create a database. This is scary/bureaucratic as it involves permissions issues etc. and occasions some level of slowdown
  2. Create two tables 
    CREATE TABLE todolist (
  id SERIAL PRIMARY KEY,
  item varchar(100) NOT NULL,
  owner_id integer
);

CREATE TABLE team_members (
  id SERIAL PRIMARY KEY,
  first_name varchar(30) NOT NULL
);
  3. Create some list items… 
    stooges_todo=# INSERT INTO todolist (item) VALUES ('Get gas');
stooges_todo=# INSERT INTO todolist (item) VALUES ('Plot season 4');
stooges_todo=# INSERT INTO todolist (item) VALUES ('Buy milk');
stooges_todo=# INSERT INTO todolist (item) VALUES ('Find toupée');
  4. Create some possible owners… 
    stooges_todo=# INSERT INTO team_members (first_name) VALUES ('Larry');
stooges_todo=# INSERT INTO team_members (first_name) VALUES ('Curly');
stooges_todo=# INSERT INTO team_members (first_name) VALUES ('Moe');
  5. Create some binding data…(almost there!) 
    UPDATE todolist SET owner_id = 1 where id=1;
  6. Print a query of owned todo items using JOIN: 
    stooges_todo=# select item, team.first_name from todolist JOIN team_members as
team ON todolist.owner_id=team.id;
  item   | first_name
---------+------------
 Get gas | Larry
(1 row)

All of these steps get us to the equivalent of the two-sheet spreadsheet. As someone experienced (but admittedly rusty) with Postgres syntax this took me about ten minutes to complete. A spreadsheet version of this application took me about two minutes.

On top of that, we’ve not even added the email address! Back to the console.

First, we have to update the structure of the database:

stooges_todo=# ALTER TABLE team_members ADD COLUMN email_address varchar(100);
ALTER TABLE

Add data for each row for the new column:

stooges_todo=# UPDATE team_members SET email_address = 'larry@example.com' where id=1;
stooges_todo=# UPDATE team_members SET email_address = 'curly@example.com' where id=2;
stooges_todo=# UPDATE team_members SET email_address = 'moe@example.com' where id=3;
UPDATE 1

And, lastly, update our query:

stooges_todo=# select item, team.first_name, team.email_address from todolist JOIN team_members as team ON todolist.owner_id=team.id;
  item   | first_name |   email_address
---------+------------+-------------------
 Get gas | Larry      | larry@example.com
(1 row)

Ultimately, at a certain level of complexity, insights will fall back to needing complicated SQL statements. By asserting this technical barrier to entry, we can be assured that the data is well-kept, but that barrier to entry also ensures that getting data out of the system will remain the prerogative of a technical caste and their capacity for non-mission-critical work.

Some portion of this problem can be mitigated by 3rd-party applications or by building alternative interfaces (web applications, etc.); however, this requires development and maintenance.

Even if we suppose SQL knowledge is more diffuse than it tends to be, certain problems will surface with multiple individuals wanting to update the database’s schema and content in parallel.

Schema Problems

What if I have added new columns with a name that disagrees with yours? What if I’ve made a more “correct” database design, but your “quicker and dirtier” ships faster, achieves mind-share faster, and has an easier learning curve? Do we adopt “fast” or “correct” and who adjudicates? On top of that, how would we reconcile that? We’d have to revert to a common point, agree on synthesis and have all our downstream consumers update their behaviors. It’s far from ideal.

Content Problems

Lastly, how do we make the call as to whose content updates win? We’re going to invent new processes around the database in order to get this work done: locks, signoffs, policy, etc. This is highly non-optimal. This sort of bureaucratic front-loading of efficacious use of the accounting limits its value and possibly even dooms it.

Rejecting the Standard Tools

Given a choice between a spreadsheet (no integrity; high usability) and a database (high integrity; low usability), we’re clearly lacking a middle path solution. I believe the comprehension of this gap is the genius behind AirTable.

Next: AirTable as Database/Spreadsheet Synthesis