By making use of the SQL abstraction layer we are able to use SQL to its full potential and, for instance, query information from multiple tables and pinpoint the exact data we need. However, currently our code base contains too many queries that overuse the possibilities of our SQL abstraction layer. More precise, at various locations the code fails to properly separate concerns which unnecessarily increase coupling and dependencies. We should be aware of this problem, fix current code and try to prevent it in the future.
Symptom
The problem that SQL code does not properly separate concerns can easily be identified. The main symptom is that some function accepts some kind of identifier that it is the key of a certain table while in fact the function needs an identifier to an other table. Because the function receives the wrong identifier, the code within the function has to follow a foreign key path from the table it has an identifier for, joining tables along the way, finally ending up at the table and the identifier it really needs.
Example:
A function performs an action on a calculation that resulted from an advice. The function currently accepts an advice identifier and follows the calculation identifier recorded in the advice to the calculation and then it performs its operation on the calculation.
Problem
The problem with not properly separating concerns in SQL code is that the degree of coupling and dependencies increases in the code. The code is not usable anymore in isolation but instead needs to be executed with some preconditions. Furthermore, querying the same tables, joining them over and over again, wastes a lot of queries.
Solution
The solution to the problem is easy. One should identify the key and table the function is really dependent on and rewrite code accordingly.
Example:
The function that performs an operation on a calculation should accept a calculation identifier instead of an advice identifier. The calling code should be modified to provide the calculation identifier. This identifier probably already is available, especially when the calling code already properly separates concerns itself.