Principle of Simplest Query Operations
We present a simple style rule for SQL select queries with the intention
to make it easier to develop queries.
The rule also applies to Access Query Builder in which context
the rule was developed.
I am tempted to call it the Law of Demeter for Queries.
The Law of Demeter promotes simple methods in object-oriented systems
while the principle of least information promotes simple queries
in relational data bases.
Formulation
A query joins the appropriate number of tables to provide the required fields and their correct values.
A query also projects the minimum number of fields needed.
In addition, a query does exactly one of four things
-
widening : introduce a calculated field which keeps the
number of rows the same.
-
aggregation : aggregate over rows (sum, average, count etc.), i.e., using a Totals-query.
Also introduces a calculated field but changes the number of rows.
-
elimination of duplicates: eliminate duplicate rows.
-
selection: select a subset of the rows using a condition.
Leads to queries that are easier to develop and debug.
We want to have minimum information per query to not overload and confuse.
Simple queries make it explicit in which order the steps are applied.
What is minimized?
The minimum number of fields are selected (projection) to achieve the correct result.
Each query implements one task involving a join, and a minimum projection and one of widening, aggregation, elimination of duplicates and
row selection.
We minimize the operations performed by a query to one of the above four.
Note that the style rule does not constrain what can be expressed.
However, it requires more nested queries.
But the significant benefits are (1) each nested query
can be easily tested and (2) less knowledge is needed about how query evaluation works and
(3) the confusing feature combinations are eliminated.
If you need a pure join of tables, do a row selection without a condition.
References
Richard Rasala:
Access Queries.
Focus on:
Our Query Philosophy and
Our Query Style.
Karl Lieberherr and the Demeter Team:
Law of Demeter
Private Communication with Ken Baclawski fall 2014.
Teaching Materials
PowerPoint Slides
Files:
PrincipleOfLeastInformation2.pptx and
DebuggingWithPrincipleOfLeastInformation.pptx
Karl Lieberherr, Spring 2015