Refactoring with Loops and Collection Pipelines: Grouping flight records | Hacker News Reader

Refactoring with Loops and Collection Pipelines: Grouping flight records

martinfowler.com

19 pointskilimchoi7 comments

I understand the utility and have used linq to do this type of thing now and again, but I like to do this kind of labor in SQL.

Working with collections is what it's suited for, so why try to do this anywhere else?

I am a big fan of SQL myself, but sometimes data doesn't live in a database. (In this case, it looks like his sample data is coming from a JSON API call).

I suppose you could create a database to store the data and then query it, but that might be adding more overall complexity to your system than would be saved by using SQL instead.

I am a proponent of moving as much complex logic away from SQL and into the application layer as possible. I'm not talking about ordering and grouping, but truly complex business logic. I have seen tremendous benefits over the years in following this pattern where applicable.

To name a few:

[0] Application code is much easier to test. This is by far the biggest factor. I recently rewrote a legacy application that was mostly driven by stored procedures hundreds of lines long, to a service driven application with database interaction limited to lookups and saves. We were able to wrap hundreds of automated tests around that process. The confidence we gained and freedom to improve upon it down the road was unbelievable.

[1] High level application code is much easier to read and understand than really complex SQL; and therefore is much easier to support. This is extremely important when you have a large team of developers. Of course, bad code is bad code, whether it's in the application or database, however I find this rule is still generally true.

[2] Depending on your database vendor, the application layer is usually much cheaper to scale horizontally. In the enterprise, I've worked mostly with SQL Server and there is no comparison in the licensing cost involved in standing up a new database server vs an application server.

Of course, this is not always the case but from my experience this approach has made the most sense.

One downside to doing these calculations in the application, vs the DB: You have to transfer more data from the DB over a finite sized connection than you do if it's in SQL (stored procs or no). For better or worse, the DB is well tuned for these forms of workflows, most general application programming languages are not.

This doesn't apply to data which is being manipulated before stored, of course.

Yes, absolutely. That is one downside to this approach (and there are more). Even though you are moving the data to the calculations, I would still contend that this approach can be as efficient, if not more efficient, depending on how well your app layer is scaled. However, this is the challenge of designing software, weighing the pros and cons and making the best decision based on your situation.

https://news.ycombinator.com/item?id=9806121

this one is the 4th installment.

Reply on news.ycombinator.com