Back in 2008 I began working for a startup that had a product for government administration. The thing with the government space it’s that they invest a lot in IT infrastructure: licenses, hardware, custom software and so. This makes it almost impossible to pitch a new system if it can’t leverage what the institution already has. Over time we came to have several customers using different databases. How did we manage to deploy the system to customers using Oracle, MS SQL and pretty much almost any database as a data store? As I have been on different projects since then, whenever I found a restriction imposed by a technology (like a DB system) I found myself asking this question once again.

Achieving database system independence

Government can be a complex beast, with all it’s regulations and rules. It was challenging to make the product flexible enough so it could be easily adapted to a new customer requirement’s. Heck it even had a scripting engine! But looking back I believe that this was possible thanks to 2 things: using an ORM and putting no logic on the DB.

By using an ORM we achieved data type representation independence. That is, all the data needed by the system is represented by the objects in the system regardless of how they are stored. This gave us the liberty to switch from one db technology to another without having to change the code. All we had to deal with was changing the db provider, who had the knowledge to serialize the objects to each DB. We could write our own provider to save to files, had we wanted to.

Since the beginning it was clear to us that having logic hosted in the DB was not a good idea. Had we gone that route, we would had a hard time porting the system from a DB to another. It’s also harder to test. The funny thing is that a lot of developers still follow this practice. Even more, they embrace it!

Encapsulation as a system attribute

I have talked about encapsulation as a code attribute before. However the same principle can be applied to a system as a whole. By having a data representation that is independent of any external technology, we can reduce the impact of external forces (like changing the data storage or presentation technology). That’s the purpose of the so many architectural guides out there: to have the logic resilient to changes from the outside. In my experience this is a natural effect when following this same principle at the object level.

However having the business logic in more than one layer (presentation, data or anything else) always lead to code that is harder to maintain and test. Define a layer that holds your business logic (domain layer) and put all your business rules and logic in there. In other words encapsulate the business logic in a single layer.

A word about SQL

An interesting fact that makes me curious is that the standard SQL specification has no control flow structures. That is why is so hard to move logic from a DB system to another: each one implements it’s own way. But why deal with this when you can use a general purpose language that implements all of this from the get go? If the ANSI SQL does not implement it, why force it?