3 ms·

You're arguing that business rules are easier to express in other programming language, but the point of the parent post (and SQL) is that business rules are ea

You're arguing that business rules are easier to express in other programming language, but the point of the parent post (and SQL) is that business rules are easier to enforce in SQL. It's trivial to write some code verifying "Is this value one of this list of values", but writing such code not ensure that this constraint will actually be met in 100% of your past and future data. It's very difficult to guarantee that a business constraint expressed in your client app is actually enforced - there can be different versions of your app applying the constraint differently, there can be multiple apps accessing the datastore, there could be manual interventions in various ways to the datastore, the app could have code paths that may cause data insertion or alterations without running that verification in certain conditions, etc; so for all intents and purposes you can't really rely on that constraint.

lmm 6y ago
> It's very difficult to guarantee that a business constraint expressed in your client app is actually enforced - there can be different versions of your app applying the constraint differently, there can be multiple apps accessing the datastore, there could be manual interventions in various ways to the datastore, the app could have code paths that may cause data insertion or alterations without running that verification in certain conditions, etc; so for all intents and purposes you can't really rely on that constraint. If you have business logic in your datastore then that puts you in much the same position though. If you have a trigger you might have data written before it was introduced, or queries that ran with it disabled. If you're gradually rolling out a new version then you might have some data that follows a constraint and some that doesn't. And so on.
- Akronymus 6y ago
  For triggers, you can run a procedure that executes it for all past data. Or write a procedure that updates the data to a valid state. When gradually rolling out, you can have adjusted stored procedures that deal with the different versions, and turn off the old one when it is no longer in use. So, I fail to see the problems you mentioned.
  - lmm 6y ago
    And with good practices (mainly a decent type system that lets you distinguish between checked and unchecked values) you'll have no problems with constraints in the application layer either.
    - Akronymus 6y ago
      I don't think we'll come to an agreement in regards to that, so, I am gonna voice my disagreement and move on. It was informational that I saw your viewpoint, even though I disagree. In the future I will definitely try and reevaluate my viewpoints to see if maybe I am wrong after all.
    - marcosdumay 6y ago
      As long as your application layer is a single homogeneous application running the same code. That is approximately never the case. Current widespread practices are moving away from it, with multi-services, and old fashioned practices of mixing customized and off the shelf tools basically forbid it.
      - lmm 6y ago
        Current practice is moving away from the idea of having your storage layer be a single homogeneous datastore as well.
        SahAssar 6y ago
        So where are you proposing the consensus be placed? A lot (I'd argue most) applications need consensus for them to be stable and reliable in the long term, so either you place it in the datastore or in the logic processing. Where else would you place it?
        lmm 6y ago
        I prefer to see the whole system as a succession of stream transformations (https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou...). If you view the sequence of input events as first-class and the "current state of the world" as derived, then a lot of problems go away. You need a datastore that can give you a consistent answer as to what order events occurred in, but it's a lot easier to make appending to an append-only list atomic than to make arbitrary state computations ACID. For downstream derived computations, basically you either make the causal relationship explicit, or accept that you have eventual consistency. The only case where you can have inconsistency is where you have a "diamond" in your computations (i.e. you compute B that's derived from A and C that's also derived from A, and you compute D that's derived from B and C). So you figure out the business implications and either accept it or eliminate the diamond (by computing (A, B) from A and (B,C) from (A, B) instead of computing B and C separately from A). You will also get inconsistency if you do some totally ad-hoc query that's not part of your existing pipelines, but usually that's the kind of reporting query that doesn't need to be 100% consistent; if you do need a consistent version of that then the best approach is to take a regular snapshot, which can be consistent. Basically you have a lot more precise control, you have causal relationships where you have explicit dependencies, so you have the level of consistency that you need for all your operational stuff, but you don't have a globally consistent realtime view of everything. It may take more work up front, but IME the notion of that kind of global consistency is a lie in a distributed world; even if you just have a basic webapp then you can't actually achieve the kind of consistency that that model pretends you have, because what the user is viewing in their browser (and then potentially making changes based on) at any given time is not necessarily the same as what's in the central database. To get back to the question, this means that if inconsistency is due to an actual bug, it's pretty easy to solve: fix the bug and then regenerate from the original events. If you don't understand why data is inconsistent then you can always look back to the source events and figure out what it should be.

lmm 6y ago

> It's very difficult to guarantee that a business constraint expressed in your client app is actually enforced - there can be different versions of your app applying the constraint differently, there can be multiple apps accessing the datastore, there could be manual interventions in various ways to the datastore, the app could have code paths that may cause data insertion or alterations without running that verification in certain conditions, etc; so for all intents and purposes you can't really rely on that constraint. If you have business logic in your datastore then that puts you in much the same position though. If you have a trigger you might have data written before it was introduced, or queries that ran with it disabled. If you're gradually rolling out a new version then you might have some data that follows a constraint and some that doesn't. And so on.

Akronymus 6y ago
For triggers, you can run a procedure that executes it for all past data. Or write a procedure that updates the data to a valid state. When gradually rolling out, you can have adjusted stored procedures that deal with the different versions, and turn off the old one when it is no longer in use. So, I fail to see the problems you mentioned.
- lmm 6y ago
  And with good practices (mainly a decent type system that lets you distinguish between checked and unchecked values) you'll have no problems with constraints in the application layer either.
  - Akronymus 6y ago
    I don't think we'll come to an agreement in regards to that, so, I am gonna voice my disagreement and move on. It was informational that I saw your viewpoint, even though I disagree. In the future I will definitely try and reevaluate my viewpoints to see if maybe I am wrong after all.
  - marcosdumay 6y ago
    As long as your application layer is a single homogeneous application running the same code. That is approximately never the case. Current widespread practices are moving away from it, with multi-services, and old fashioned practices of mixing customized and off the shelf tools basically forbid it.
    - lmm 6y ago
      Current practice is moving away from the idea of having your storage layer be a single homogeneous datastore as well.
      - SahAssar 6y ago
        So where are you proposing the consensus be placed? A lot (I'd argue most) applications need consensus for them to be stable and reliable in the long term, so either you place it in the datastore or in the logic processing. Where else would you place it?
        lmm 6y ago
        I prefer to see the whole system as a succession of stream transformations (https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou...). If you view the sequence of input events as first-class and the "current state of the world" as derived, then a lot of problems go away. You need a datastore that can give you a consistent answer as to what order events occurred in, but it's a lot easier to make appending to an append-only list atomic than to make arbitrary state computations ACID. For downstream derived computations, basically you either make the causal relationship explicit, or accept that you have eventual consistency. The only case where you can have inconsistency is where you have a "diamond" in your computations (i.e. you compute B that's derived from A and C that's also derived from A, and you compute D that's derived from B and C). So you figure out the business implications and either accept it or eliminate the diamond (by computing (A, B) from A and (B,C) from (A, B) instead of computing B and C separately from A). You will also get inconsistency if you do some totally ad-hoc query that's not part of your existing pipelines, but usually that's the kind of reporting query that doesn't need to be 100% consistent; if you do need a consistent version of that then the best approach is to take a regular snapshot, which can be consistent. Basically you have a lot more precise control, you have causal relationships where you have explicit dependencies, so you have the level of consistency that you need for all your operational stuff, but you don't have a globally consistent realtime view of everything. It may take more work up front, but IME the notion of that kind of global consistency is a lie in a distributed world; even if you just have a basic webapp then you can't actually achieve the kind of consistency that that model pretends you have, because what the user is viewing in their browser (and then potentially making changes based on) at any given time is not necessarily the same as what's in the central database. To get back to the question, this means that if inconsistency is due to an actual bug, it's pretty easy to solve: fix the bug and then regenerate from the original events. If you don't understand why data is inconsistent then you can always look back to the source events and figure out what it should be.