The Debate

So I was talking with a very intelligent colleague of mine today about the role of databases. This isn't the first time, and probably won't be the last. The debate, in this particular case, was two-fold:

1. Are databases strictly infrastructure?
2. Should business logic live in databases?

I've heard arguments on both sides in each of these cases, but never could really pinpoint why one would be better than the other. And even now, from a developer perspective, I don't have an answer. But from an architectural standpoint, I believe I do. Let's discuss.

Are Databases Strictly Infrastructure?

I split this debate into two opposing viewpoints:

1. Databases are strictly infrastructure, much like file systems and networking.
2. Databases are components of the system and are more than storage of data.

Now when you think database, you may think data store. And you'd be right. It is a data store, just like a file system stores blobs of file data on disk. In this case, the database is a part of the infrastructure of your system and only holds information. You store and retrieve information just like any other system.

But not all database engines are created equally.

For instance, Sqlite is a database storage engine that strictly holds data. It has very little in the way of custom functions and has no mechanism for stored procedures. In this case, I would agree, it is strictly infrastructure to hold data.

However, SQL Server is chock full of functionality. We have custom functions and procedures, CLR integration, and insertable views. These features allow for a deeper usage of the engine beyond simple data storage. Instead, we can create routines and logic with the data, allowing for less "code" elsewhere in the system. These features can be shared across multiple applications as well, even if they are not developed with the same technologies. In this way, I would argue, that SQL Server can be a component of the system.

Now this isn't to say that business logic SHOULD be with the data. And from a developer's point of view, I understand this. As developers, we like having control over this logic so it can be adjusted, by us, accordingly. However, I am not saying this is the correct viewpoint.

Let's go in more depth in the next section about my reasoning.

Should Business Logic Live in Databases?

Again I split this debate into two factions:

1. Databases should contain no business logic except for performance optimization reasons.
2. Databases should maintain some level of business logic for ease of deployment and changes.

The thought behind no business logic being in the database, goes back to the last section where databases are infrastructure and code is the business logic. If you put logic in databases and code, now you have two places where logic is sitting and how do you know where it is?

This is an absolutely valid point. And a good one at that. Having logic in both places, especially if you feel that databases are strictly infrastructure entities, makes no sense. Instead, focus the logic in one place.

What about the ease of changing when we have logic in the database engine? Well this is also true. It is well understood that deploying a SQL change is notoriously easier than code changes. Granted, the divide has shrunk in recent years; we can still say that overall, changes to a database is easier to perform and deploy. In fact, it is so easy to change, that shortcuts are often taken with database changes, in order to avoid code deployments. Is this wrong? Well that's debatable (and one for another article).

So here is where I question the "best practices" here. Let's look at this from an architectural viewpoint. Let's first assume that the database we are using is a component, and not strictly infrastructure. This means that we can look at the database as a component of the system with it's own logic and purpose.

By viewing the database this way, stored procedures having business logic, makes sense. In fact, a shared database with business logic, means that re-use across disparate systems (that can directly access the database) is much easier. More often than not, multiple software packages talk with the same database, especially in the enterprise. This is an important point.

If stored procedures are designed in a way to act like an interface and all tables are hidden from outside use, we have effectively created a shared component. This allows for a few things to happen:

1. All applications access the same procedures and are consistent.
2. Restructuring the database now becomes possible as everyone is utilizing the "interface."
3. Deploying changes to the interface "implementation" (i.e. stored procedures) is easier and faster.

Some may say, "Why don't you create a web service that all applications access?" And I would say, "Absolutely!" This is a solution to the problem. In fact, if the resources are available to add this extra layer, go ahead and do it. The stored procedures are no longer needed, as long as all services are accessing this service. Of course, this adds performance overhead and a layer of indirection, it has the benefit of adding a layer for outside consumption of the data.

I think this is the key: the design of the interface (i.e. parameters of the stored procedures) must remain consistent and be generalized to ignore the underlying structure of the database. If the stored procedures are used as a simple re-creation of the database structure, you've gained nothing at all, and you might as well leave the SQL inline with code.

Conclusion

I'm not saying my view is the "right way," but it is valid. It is a matter of perspective. By peeling back the notions of "right" vs "wrong" and getting rid of bias from past experience, there are other options and ways to look at this debate. There are good things about stored procedures, namely speed of updates and performance. We should not toss these aside. Updating quickly is a common need in development, and efficiency is important. The quicker software can be changed and fixed, the better the system (in my opinion).

However, more times than not, we utilize other patterns because of the lack of discipline from a developer. We must accommodate for the careless developers that abuse the patterns. Of course, this should be taken into account, it shouldn't be a hindrance to finding the right approach for a problem.

In summary, the "it depends" answer applies ere. As with all things, what is best in what situation will depend on the number of applications sharing the database, and how they're doing it. And it will depend on the type of developers working in your organization. Either way, I hope this provides a different view on the argument...however, who knows, it may not be new at all...