Extra for experts on database normalization and its benefits and tradeoffs. Our database course got as far as 3NF; “you are like little baby” etc., this post gets to “virtual 6NF”!!!! !!!

As software developers, we can solve any problem by adding one level of abstraction, and database modeling is not an exception. I’m going to show that any database could be represented as a number of relations in either 6NF or 4NF, and why it’s useful.

Investigation of the PostgreSQL query planner:

The basic promise of a query optimizer is that it picks the “optimal” query plan. But there’s a catch - the plan selection relies on cost estimates, calculated from selectivity estimates and cost of basic resources (I/O, CPU, …). So the question is, how often do we actually pick the “fastest” plan? And the truth is we actually make mistakes quite often.

Insight into (SQL) query planning:

This is a very simplified approach to thinking about join ordering, it ignores things like statistics, indexes, outer joins, and different join algorithms, but it is an important intuitional foundation.

Discussion of different approaches to complex multi-stage SQL queries, in light of one specific example.

Pragmatics of database design: the argument for soft-delete (marking records as deleted rather than actually removing them).

Let’s say our marketing department decides to delete an item from the catalog. Should all previous orders containing that item just disappear? And cascading farther, should all invoices for those orders be deleted as well? Going on, would we have to redo the company’s profit and loss statements?

Tony Finch (April 2025) points out an important exception to the idea of just using IsDeleted flags, however:

The main counterexample is data protection law. You have to be able to delete personal data. This article predates the GDPR so it has a reasonable excuse for not taking it into account.