4 ms·

How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? no interest in writing my whole appli

How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? no interest in writing my whole application as triggers or whathaveyou. It's a bit of a middle ground, perhaps? Yeah, I think our position is often misunderstood as "put everything in the database layer." That is definitely not how I think. I just think moving stuff to the database layer is one possible tool in the toolshed. One thing I like to use it for is when it's a "low level" database concern, like generating an audit trail whenever a particular table is changed. To me, populating that audit trail is clearly a database concern and not an application concern. (And if there are heavy writes to that table, the performance difference may be large)

jmull 3y ago
> How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? Not the previous poster but... I would think of it as an application layer that is running at the database. It would make the most sense for logic that is closely coupled to data access... exactly what that is depends on your app. Low-level access control policies... maybe you need to dig data out of a bunch of tables and turn it in to a hierarchy based on complicated user prefs also stored in the database... Or the opposite where you have complex data coming in that needs to be written to a bunch of tables, especially when there's back and forth. I don't think very many should try to put their whole app in there. Database compute tends to get expensive and complicated to scale, so stuffing things in there just because you can might run in to trouble. Not to mention (except for SQLite) the runtime environment is seriously constrained in all kinds of ways. The previous post mentions triggers but I don't know what those have to do with this. The chance that triggers are right solution to any given problem is approximately 0% in my experience.
- sodapopcan 3y ago
  I think this is essentially what I meant? In any event, I responded a little clearer but ya, shaping data and moving it around is a big thing I feel should be done in the DB. Having spent time in the rails world for several years, I worked with a lot of people who didn't want to do ANYTHING at the DB level. Like, obviously they'd do joins and things ActiveRecord could do easily, but they'd be fine pulling in a bunch of rows and reducing them in Ruby. That drove me nuts. I'm not an optimization junky at all but I do not like that in the slightest. Not only is it wasteful and slower, in my experience it's also more error prone.
  - JohnBooty 3y ago
    pulling in a bunch of rows and reducing them in Ruby [...] Not only is it wasteful and slower, in my experience it's also more error prone. Yeah. A lot of problems happen for one of two reasons. The application developers don't wrap the whole thing in a transaction, and open themselves up to consistency problems. Or they do wrap a bunch of database round trips up in a transaction and open themselves up to contention issues, deadlocks, etc. In both cases, those problems generally never show up in local or test environments. Only in prod... under load.
sodapopcan 3y ago
All aggregating and all math I do in the db. Stuff like taxes, royalties, any reporting stuff (that should probably be in a column store). It may seem like a no-brainer to do that but you'd be surprised. I worked at a fintech briefly and ALL the math was done in Elixir which surprised me. I also usually like to do permissions in SQL, ie, selecting a record based on a permission as opposed to grabbing the record then checking (I go back and forth on this). I do like to have everything that is going on represented in the application code, though, so I've never actually written a trigger in my life... I did write a stored procedure once for generating unique product codes... I never said I was a particularly good engineer, lol. But ya, even in the case of a cascading delete or something I still like to spell that out in application code so there are no surprises. Of course, I've never gotten to work on a team where everyone felt this way. Audit trails seems like an interesting case I have thought about before (I have been a part of building audit trails at the app level before). It's something I've been curious about but never looked into. I think it depends on how detailed they have to be. Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that. I did talk to someone who worked somewhere where that was the strategy.
- JohnBooty 3y ago
  I feel like audit trails are under-discussed and under-rated. Great for debugging (obviously, not as the sole debugging information, hopefully) and indispensible for covering your ass at times. If people screw up and put bad data in they frequently will blame the app and as a developer the burden of truth is often on you. Especially if the app has had bugs in the past... and what app hasn't? Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that Yeah, doing it indiscriminately is wasteful to the extreme, no arguments! You would want to be judicious about the tables for which you employ it, the size of the data, frequency of updates, etc. In almost all cases you will surely want some kind of automated pruning ability. (Of course, this all applies at the app level as well, no difference here) What's unique about triggers is that they have access to the "old" and "new" versions of a row. Implementation varies but generally it's something like this Postgres example - note the special `OLD` and `NEW` tables. CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE FUNCTION check_account_update(); Now, that specific is obviously "application" or "business" logic and it can be debatable if that should go into the database. But audit trails are more of a pure data layer concern IMO.

jmull 3y ago

> How did you decide which bits of business logic would live in the database as opposed to at the application layer(s)? Not the previous poster but... I would think of it as an application layer that is running at the database. It would make the most sense for logic that is closely coupled to data access... exactly what that is depends on your app. Low-level access control policies... maybe you need to dig data out of a bunch of tables and turn it in to a hierarchy based on complicated user prefs also stored in the database... Or the opposite where you have complex data coming in that needs to be written to a bunch of tables, especially when there's back and forth. I don't think very many should try to put their whole app in there. Database compute tends to get expensive and complicated to scale, so stuffing things in there just because you can might run in to trouble. Not to mention (except for SQLite) the runtime environment is seriously constrained in all kinds of ways. The previous post mentions triggers but I don't know what those have to do with this. The chance that triggers are right solution to any given problem is approximately 0% in my experience.

sodapopcan 3y ago
I think this is essentially what I meant? In any event, I responded a little clearer but ya, shaping data and moving it around is a big thing I feel should be done in the DB. Having spent time in the rails world for several years, I worked with a lot of people who didn't want to do ANYTHING at the DB level. Like, obviously they'd do joins and things ActiveRecord could do easily, but they'd be fine pulling in a bunch of rows and reducing them in Ruby. That drove me nuts. I'm not an optimization junky at all but I do not like that in the slightest. Not only is it wasteful and slower, in my experience it's also more error prone.
- JohnBooty 3y ago
  pulling in a bunch of rows and reducing them in Ruby [...] Not only is it wasteful and slower, in my experience it's also more error prone. Yeah. A lot of problems happen for one of two reasons. The application developers don't wrap the whole thing in a transaction, and open themselves up to consistency problems. Or they do wrap a bunch of database round trips up in a transaction and open themselves up to contention issues, deadlocks, etc. In both cases, those problems generally never show up in local or test environments. Only in prod... under load.

sodapopcan 3y ago

All aggregating and all math I do in the db. Stuff like taxes, royalties, any reporting stuff (that should probably be in a column store). It may seem like a no-brainer to do that but you'd be surprised. I worked at a fintech briefly and ALL the math was done in Elixir which surprised me. I also usually like to do permissions in SQL, ie, selecting a record based on a permission as opposed to grabbing the record then checking (I go back and forth on this). I do like to have everything that is going on represented in the application code, though, so I've never actually written a trigger in my life... I did write a stored procedure once for generating unique product codes... I never said I was a particularly good engineer, lol. But ya, even in the case of a cascading delete or something I still like to spell that out in application code so there are no surprises. Of course, I've never gotten to work on a team where everyone felt this way. Audit trails seems like an interesting case I have thought about before (I have been a part of building audit trails at the app level before). It's something I've been curious about but never looked into. I think it depends on how detailed they have to be. Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that. I did talk to someone who worked somewhere where that was the strategy.

JohnBooty 3y ago
I feel like audit trails are under-discussed and under-rated. Great for debugging (obviously, not as the sole debugging information, hopefully) and indispensible for covering your ass at times. If people screw up and put bad data in they frequently will blame the app and as a developer the burden of truth is often on you. Especially if the app has had bugs in the past... and what app hasn't? Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that Yeah, doing it indiscriminately is wasteful to the extreme, no arguments! You would want to be judicious about the tables for which you employ it, the size of the data, frequency of updates, etc. In almost all cases you will surely want some kind of automated pruning ability. (Of course, this all applies at the app level as well, no difference here) What's unique about triggers is that they have access to the "old" and "new" versions of a row. Implementation varies but generally it's something like this Postgres example - note the special `OLD` and `NEW` tables. CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE FUNCTION check_account_update(); Now, that specific is obviously "application" or "business" logic and it can be debatable if that should go into the database. But audit trails are more of a pure data layer concern IMO.