5 ms·

I make heavy use of SQL in my daily work. A lot of our business logic lives in the database as views, triggers and various functions. I actually find that taki

by nadaviv 13y ago

I make heavy use of SQL in my daily work. A lot of our business logic lives in the database as views, triggers and various functions. I actually find that taking this logic away from the app code and into the database is one of the best architectural decisions we took. I think the primary issue some people have with doing that is that they don't know working with databases well enough, and consider them a dumb place to store data, and nothing more. This is what makes it difficult to maintain. One thing that I do agree with is that the SQL code isn't portable - but its the same as your Ruby/PHP/NodeJs/whatever code not being portable. I don't see that as an issue.

umsm 13y ago
In my experience: most people that don't know proper DB design and SQL will do two things: - Over-utilize stored procedures, views, etc. - Implement many obscure "hacks" There is nothing wrong with using built-in database functionality, but it can be over done.
btilly 13y ago
From a software engineering perspective, this is entirely backwards. Databases offer a great way of looking at your data. However they have very poor stories around version control, rollout, rollback, separating metadata from data, and creating multiple parallel environments for production, qa, and every dev engineer. Yes, I know that database vendors claim otherwise, but compared to what is standard for most programming environments these days, it is a major limitation. But it doesn't stop there. If you are lucky enough to get a busy site, you will find that the database is the inevitable bottleneck that can't simply be scaled by putting machines in parallel. The more work you push down to that, the sooner you'll hit that bottleneck, and the harder it is to fix once you do. When you've experienced, as I have, the joys of periodic outages because the database choked at a million dynamic pages/day, you'll value moving logic out of the database, the ability to put caching in front of it, etc. Now to all of this the database vendors have a trump card, what happens if multiple applications access the database? If your business logic is there, then you're fine, otherwise you're reimplementing that logic multiple times! The thing is that in the 15 years that I've seen people making that argument, I've not encountered a lot of organizations opening up a business critical transactional database for random applications. And in practice it is easier and saner to put an RPC layer on top of of your existing app, in which case you get the business logic that way. I guess I should somewhere note that I spent several years of my life with my primary job being to write fancy SQL. I was also the go to person on the software development side for "get us to scale the database better". I do not criticize databases out of a lack of knowledge of how to use them. I criticize them based on a large amount of experience with how to make the most effective use of their capabilities.
- asdf1234 13y ago
  There's nothing terribly complicated about recursive queries and common table expressions, and for the use cases in which they're commonly used (hierarchical data being one of those) they perform significantly better than all of the popular SQL alternatives in almost every case.
  - btilly 13y ago
    I was referring to putting business logic in your database, and not any particular query technique.
    - jeffdavis 13y ago
      Sometimes it just saves you huge amounts of time and code to do something in the database though. For instance, if you need to join against a remote data source, you can: 1. Write a bunch of code to pull the data from both sources and join it in the application. You have to pick a join algorithm at the beginning, and if your data changes you may need to rewrite it later. 2. Add a remote table in the database and just do the join there. The database can use statistics to pick a join algorithm for you. I'm sure there are cases where you'd pick #1. But in many cases, the database offers a feature that allows you to implement a solution and call it "done" in minutes or hours where an application-based solution would drag on for days or weeks and carry a larger maintenance burden.
      - btilly 13y ago
        Please go to https://news.ycombinator.com/item?id=5934136 https://news.ycombinator.com/item?id=5934136 and look at the final paragraph. Yes, I'm well aware of what databases can do for me, and have made a good living getting them to jump through hoops to do their thing. However part of knowing how to use a tool well is knowing its limitations. And from what I've learned, a web-based CRUD application is usually better off with business logic in the web layer, and not in the database. Also I have absolutely no idea why you brought up joining against remote data sources. However I've had to do it against multiple databases over the years. And in my experience with Access, Sybase, Oracle, PostgreSQL and MySQL, I found that for all but the simplest cases, #1 was preferable. For each of those databases I can name a case where I started with #2 with clear issues, and wound up with #1 working well. Why? Because if you understand query performance, you can make #1 work well. And databases tend to do a horrible job of understanding what's going on with remote data sources and picking good query plans for them. Which can cause #2 to suck in hard to fix ways. (Plus with PostgreSQL specifically there were security issues that made the DBA unwilling to open up one database remotely to the other. He was willing to let my application access both, but didn't want to allow other applications to be able to do it.) That said, most people who need access to remote data are in a simple circumstance. And I know a lot more about query performance than your average software developer. So if #2 can work for you, you should go that route.

by nadaviv 13y ago

umsm 13y ago

In my experience: most people that don't know proper DB design and SQL will do two things: - Over-utilize stored procedures, views, etc. - Implement many obscure "hacks" There is nothing wrong with using built-in database functionality, but it can be over done.

btilly 13y ago

From a software engineering perspective, this is entirely backwards. Databases offer a great way of looking at your data. However they have very poor stories around version control, rollout, rollback, separating metadata from data, and creating multiple parallel environments for production, qa, and every dev engineer. Yes, I know that database vendors claim otherwise, but compared to what is standard for most programming environments these days, it is a major limitation. But it doesn't stop there. If you are lucky enough to get a busy site, you will find that the database is the inevitable bottleneck that can't simply be scaled by putting machines in parallel. The more work you push down to that, the sooner you'll hit that bottleneck, and the harder it is to fix once you do. When you've experienced, as I have, the joys of periodic outages because the database choked at a million dynamic pages/day, you'll value moving logic out of the database, the ability to put caching in front of it, etc. Now to all of this the database vendors have a trump card, what happens if multiple applications access the database? If your business logic is there, then you're fine, otherwise you're reimplementing that logic multiple times! The thing is that in the 15 years that I've seen people making that argument, I've not encountered a lot of organizations opening up a business critical transactional database for random applications. And in practice it is easier and saner to put an RPC layer on top of of your existing app, in which case you get the business logic that way. I guess I should somewhere note that I spent several years of my life with my primary job being to write fancy SQL. I was also the go to person on the software development side for "get us to scale the database better". I do not criticize databases out of a lack of knowledge of how to use them. I criticize them based on a large amount of experience with how to make the most effective use of their capabilities.

asdf1234 13y ago
There's nothing terribly complicated about recursive queries and common table expressions, and for the use cases in which they're commonly used (hierarchical data being one of those) they perform significantly better than all of the popular SQL alternatives in almost every case.
- btilly 13y ago
  I was referring to putting business logic in your database, and not any particular query technique.
  - jeffdavis 13y ago
    Sometimes it just saves you huge amounts of time and code to do something in the database though. For instance, if you need to join against a remote data source, you can: 1. Write a bunch of code to pull the data from both sources and join it in the application. You have to pick a join algorithm at the beginning, and if your data changes you may need to rewrite it later. 2. Add a remote table in the database and just do the join there. The database can use statistics to pick a join algorithm for you. I'm sure there are cases where you'd pick #1. But in many cases, the database offers a feature that allows you to implement a solution and call it "done" in minutes or hours where an application-based solution would drag on for days or weeks and carry a larger maintenance burden.
    - btilly 13y ago
      Please go to https://news.ycombinator.com/item?id=5934136 https://news.ycombinator.com/item?id=5934136 and look at the final paragraph. Yes, I'm well aware of what databases can do for me, and have made a good living getting them to jump through hoops to do their thing. However part of knowing how to use a tool well is knowing its limitations. And from what I've learned, a web-based CRUD application is usually better off with business logic in the web layer, and not in the database. Also I have absolutely no idea why you brought up joining against remote data sources. However I've had to do it against multiple databases over the years. And in my experience with Access, Sybase, Oracle, PostgreSQL and MySQL, I found that for all but the simplest cases, #1 was preferable. For each of those databases I can name a case where I started with #2 with clear issues, and wound up with #1 working well. Why? Because if you understand query performance, you can make #1 work well. And databases tend to do a horrible job of understanding what's going on with remote data sources and picking good query plans for them. Which can cause #2 to suck in hard to fix ways. (Plus with PostgreSQL specifically there were security issues that made the DBA unwilling to open up one database remotely to the other. He was willing to let my application access both, but didn't want to allow other applications to be able to do it.) That said, most people who need access to remote data are in a simple circumstance. And I know a lot more about query performance than your average software developer. So if #2 can work for you, you should go that route.