The Simplest Thing That Can Possibly Work

Design Principles

For any database processing there will usually be several ways to achieve the same result. This page shows a series of tasks each with a couple of solutions, and explains why we consider one solution to be better than another.

There are a number of factors we use in judging which solution is better:

• Does It Work?

  Surprisingly, we do see solutions that produce plausible but wrong answers. To be accepted, a candidate solution must work correctly in all real-world cases. That means we need tests to show that the solution is right.

• Can We Understand It?

  The solution must be written so that we can understand it. If we can't understand it then we are "programming by coincidence". That is not good enough. We must know what we are doing and write our solution so that someone else can read it in a year from now and adapt it to work for what is needed then.

• Use The Simplest Techniques

  This means:

  1. First choice is to use standard SQL (SQL statements that will work on any of the leading relational databases.) This means trying to solve the problem using join, where, group by, union, intersect, except/minus. Using these SQL functions with nested queries in the from and/or where clauses is often a good solution.
  2. Second choice is to use window functions, but only if we need to make comparisons between rows in the same result set. These are the cases where window functions make the query simpler.
  3. Third choice is to use standard SQL with temporary tables.
  4. Fourth choice is to use the stored procedure language.