I’ve been an advocate of centralizing all business rules in the database for 20+ years. Working with some extremely talented individuals the following 10 years I have been convinced that storing business rules on the database is not the only viable option, and does not have to be the best performing one.
In the last 6 years I was the lead developer for two different rules engines. In both scenarios I replaced SQL stored procedures with C# code, extracting the business rules from the database, and encapsulating it in the rules engine. In both cases, the rules were persisted in SQL Server as a mechanism to startup the rules engine.
Here are facts that are hard to criticize. In the first rule engine, average times were reduced from 60+ seconds per request to 125 milliseconds. This was using the same database server hardware, and a scalable, Azure like, rules engine host. Three rules engines ran at 2% capacity with 10X as many requests.
A second rules engine, completed just this last year, had similar performance improvement. Because the database server could only handle a limited amount of concurrent requests, everything was sent through a queue. An individual request took 5 minutes to process. Because of queue priorities, the user might not get their results for 20+ minutes.
Again, the rules were all originally implemented in stored procedures to keep it close to the data.
Replacing this system with C# code, running as a single web api process, single performance times were reduced from 5 minutes to under 2 seconds. Concurrent processes did not have an impact, so queueing was no longer required.
Users simply submit their requests, and the results return momentarily.
With both of those experiences under my belt, I find it really hard to make the statement, business rules, implemented outside of the database, cannot perform well, are fragile, should not be done. In fact, I find it a lot more powerful to simply use CRUD to communicate the database. I don’t disagree with anyone wishing to continue the practice of keeping everything in SQL. But, I can guarantee performance is not a factor.
Additionally, placing the business rules in a different tier allows you to substitute the data persistence with different technologies.
To be fair, I’m not saying that there have not been some horror stories of performance or data corruption by moving rules out of the database. However, I have seen a number of horror stories when people have implemented rules in a database. I’m not sure if I am driven more by horror stories as I am by success stories. The guys how make things work motivate me.
Thanks for all the positive comments the different positions on this topic. The biggest thing to be aware of, especially if you are just getting into database persistence, is that no matter where you place your business rules, you can design a database poorly or well. It’s a big picture kind of thing you need to get right. Be sure to seek examples providing more than simple syntax. Learn the patterns designed to provide database performance, concurrency, and accuracy of data.
Cheers,
Ben