Should business rules be enforced in both the application tier and the database tier, or just one of the two?

Question

I have been enforcing business rules in both my application tier (models) and my database tier (stored procedures who raise errors).

I've been duplicating my validations in both places for a few reasons:

1. If the conditions change between when they are checked in the application code and when they are checked in the database, the business rule checks in the database will save the day. The database also allows me to lock various records in a simpler manner than in my application code, so it seems natural to do so here.
2. If we have to do some batch data insertions/updates to the database directly, if I route all these operations through my stored procedures/functions which are doing the business rule validations, there's no chance of me putting in bad data even though I lack the protections that I would get if I was doing single-input through the application.
3. While enforcing these things ONLY in the database would have the same effect on the actual data, it seems improper to just throw data at the database before first making a good effort to validate that it conforms to constraints and business rules.

What's the right balance?

Answer 1

You need to enforce at the data tier to ensure data integrity. That's your last line of defense, and that's the DBs job, to help enforce its world view of the data.

That said, throwing junk data against the DB for validation is a coarse technique. Typically the errors are designed to be human readable rather than machine readable, so its inefficient for the program to process the error from the DB and make heads or tails out of it.

Stored Procedures are a different matter. Back in the day, Stored Procedures were The Way to handle business rules on the data tiers, etc.

But today, with the modern application server environments, they have become a, in general, better place to put this logic. They offer multiple ways to access and expose the data (the web, web services, remote protocols, APIs, etc). Also, if your rules are CPU heavy (arguably most aren't) it's easier to scale app servers than DB servers.

The large array of features within the app servers give them a flexibility beyond what the DB servers can do, and thus much of what was once pushed back in to the DBs is being pulled out with the DB servers being relegated to "dumb persistence".

That said, there are certainly performance advantages using Stored Procs and such, but now that's a tuning thing where the question becomes "is it worth losing the app server capability for the gain we get by putting it in to the DB server".

And by app server, I'm not simply talking Java, but .NET and even PHP etc.

Answer 2

If the rule must be enforced at all times no matter where the data came from or how it was updated, the database is where it needs to be. Remember databases are affected by direct querying to make changes that affect many records or to do something the application would not normally do. These are things like fixing a group of records when a customer is bought out by another customer and they want to change all the historical data, the application of new tax rates to orders not yet processed, the fixing of a some bad data inputs. They are also affected sometimes by other applications which do not use your data layer. They may also be affected by imports run through ETL programs which also cannot use your data layer. So if the rule must in all cases be followed, it must be in the database.

If the rule is only for special cases concerning how this particular input page works, then it needs to be in the application. So if a sales manager has only specific things he can do from his user interface, these things can be specified in the application.

Somethings it is helpful to do in both places. For instance, it is silly to allow a user to put a non-date in an input box that will relate to a date field. The datatype in the database should still be a datetime datatype, but it is best to check some of this stuff before you send.

Answer 3

Your business logic can sit in either location, but should not be in both. The logic should NOT be duplicated because it's easy to make a mistake trying to keep both in sync. If you put it in the model you'll want all data access to go through your models, including batch updates.

There will be trade-offs to putting it in the database vs the application models (here's a few of the top of my head):

• Databases can be harder to maintain and update than applications
• It's easier to distribute load if it's in the application tier
• Multiple, disparate dbs may require splitting business rules (which may not be possible)