Ask HN: Business Logic in the Database? | Hacker News Reader

Ask HN: Business Logic in the Database?

3 pointsboxesnlines3 comments

War story time: I once worked on a system where almost the entirety of the business logic was inside SQL stored procs. I think there were over 6000 of them, all tightly coupled, procs calling procs that call back to the original proc. Terrible performance, constant deadlocks, nobody understood how it worked and mostly everyone was too scared to touch it. So of course they ended up building even more procs around the ones that were there.

I kind of assumed it was a unique 'perfect storm' of mistakes and nobody else in the world could possibly have done the same, but I've heard from one or two other devs recently that they've seen similar and now I'm wondering if this is far more common than I'd imagined.

I feel like I'm a builder asking, "hey guys, did any of you ever work on a house where the roof was on the bottom?"

View on HN

I've seen it a couple of times.

Both cases originated in the mid 2000s - one was a large warehousing/distribution company that relied heavily on Oracle, the other was a mid-sized tech company with a weird culture split between LAMP stack customer-facing work and .net for business apps.

For the warehousing business, they set out developing their own apps to run alongside (and eventually replace) the work of Oracle consultants from the late 90s, and I suspect the stored proc addiction came about because the pattern had already been established in that earlier work.

For the tech company, it was confined to the Microsoft side of the house and was driven by a senior manager who came from a DBA background clashing with another who had a Unix background and didn't really understand the MS world.

By the time I arrived, people had begun to recognise that it wasn't an ideal situation and it had been isolated behind an Enterprise Service Bus (which brought a whole host of other problems...) with the intention of slowly being replaced. The business was bought by Private Equity and dismembered before that could happen, though.

I get the impression it was a fairly widespread Enterprise pattern in the 90s, but fell out of use as source control, CI systems, unit tests, and better development methodologies made application code the clearly better choice.

The SAP architecture stores transactions (basically programs or business logic) in the database.

It's common and I've observed it in three different industries. The reason it begins is reasonable: the proc is the only stage at which a change can be deployed without going through a release cycle, so all the hotfixes end up there, and after ten years the hotfixes make up the entire system. The circular calls arise for the same reason. Since no one dares to modify proc A, they write proc B which calls proc A and alters its output, and then someone does the same thing to proc B. The deadlocks appear once each of the proc's authors has a different view on where the transaction should start.

Reply on news.ycombinator.com