Where the application and database meet

There's an on-going debate on my team about the role of the application and the role of the database - specifically with regards to what business logic and validations go in database triggers and what business logic and validations go in the application. I'm going to give away my opinion early because it's surprising - at least it was to me. The answer is both - but that's not what was surprising to me. I was fully prepared that the answer to the problem was both and that business logic would go in the application and the database would contain the validations that the application would depend on and therefore not duplicate in application code. That is, they would each fulfill some part of the responsibility and together would accomplish the whole. What's surprising is that I'm going to suggest that the much of the same logic be duplicated in the application and in the database because while the logic overlaps, often the goal of each domain is very different, namely, the database's role is to make sure the schema's design is followed and no bad data can be created. But the application's role is store information while creating a pleasant and functional user experience.

There's a modern stream of thought held by some application developers that the database should be a dumb container for data and that all the important logic belongs in the application code. This belief has led to database design that employs surrogate keys. With this style of database, business logic is not desired in the database. The benefits of this style is mostly on the developer side and make it easy to stay in the language of choice in the application for all concerns. Not having to implement features in database's SQL dialect is good for productivity. The down-side is that the database is truly not safe for updates outside of the application layer. Ad-hoc SQL run against the database itself is not guaranteed to create bad data, and in fact guaranteed over time to do just that.

The alternate style is a DBA-centric style that employs meaningful keys in tables and many stored procedures and triggers the enforce a strong business-centric schema with logic and validations. It's not surprising that this style works best with dumb applications, possibly due to supporting multiple languages or platforms against the same database schema. With such a strong central implementation, not only is data centralized but business logic and validation is centralized as well. The down-side is that logic is implemented in a (usually) proprietary dialect of SQL and is not as familiar to application developers so side-effects of database interactions tend to feel like dark magic.

While the topic of surrogate vs. natural keys is another topic altogether, I think the middle of the road - or both roads depending on how you look at it, is the right answer. The DBAs are responsible for a sound schema that doesn't allow bad data to be created. In addition, if a record is inserted that should always trigger a corresponding entry in another part of the system for data integrity to be maintained, the database logic should do that as to not do it would leave the database in a bad state. So while there is business logic and validation in the database it's primary concern is maintaining the integrity of the state of the data. Database logic should not do anything just because it's centralized and convenient. Also, the database should never assume that an application or ad-hoc query will do the right thing. For instance, the DBAs might know for a fact that the application always sets a flag to true but should still act as if it won't and take care of it in the database.

Similarly, the application code should not rely on the database to implement validations or other business logic but implement it in the application code but also use the understanding of what is valid to shield the user from hitting validations at all by disabling or hiding fields or other techniques related to the record state that help avoid errors in the first place. The primary concern of the application is to manage the process and the flow of data entry, coordinating events and user feedback.

If duplication of logic is too unpalatable then once you identify truly identical logic - aggregated totals, for instance, that are needed by both the database and the application, the database could implement a stored procedure or view that can then be used by the application. To the application the stored procedure or view would operate like any other query.

The critical point where the application and database meet is in the transaction. The application submits a unit of work and the database verifies that it's valid. This is where it's easiest to see the separate concerns clearly. It's the application's job to bang on the door and shove the data in to get the job done for the user. In a way, the application is an advocate or proxy for the user and is lobbying on their behalf to let the transaction proceed. It's the database's job to verify and check and reject all but the most rigorously constructed set of queries and run them. In a similar way, the database represents the business and will only allow data through that has the best interest of the business in mind. It's in this way, that the best applications and databases do create the best overall platforms, accomplishing the overall goals by being adversaries with seemingly different but actually the same end.