5 ms·

>I don’t have to worry about bad devs doing stuff non-atomically and putting the db in a bad state Premise: I assume we are speaking of relational databases. T

>I don’t have to worry about bad devs doing stuff non-atomically and putting the db in a bad state Premise: I assume we are speaking of relational databases. The fact that a developer can "corrupt" the database with non-atomic stuff is an hint to me that the database probably doesn't have the right referential integrity constraints in place and probably is not normalized either. The relational model is built out of the box to keep a consistent state of the data and this is one of its main value propositions. Another danger that I didn't mention is that junior developers will be tempted to prefer "Turing like code" inside stored procedures (for loops,cursors, conditionals) instead of relational constructs (joins, subqueries,grouping,table variables). I have seen that several times and this is really a killer for database performances.

Silhouette 6y ago
The fact that a developer can "corrupt" the database with non-atomic stuff is an hint to me that the database probably doesn't have the right referential integrity constraints in place and probably is not normalized either. The relational model is built out of the box to keep a consistent state of the data and this is one of its main value propositions. This is true when interpreted the right way, but I don't think real world problems are always so tidy. Let's consider Standard Toy Example #17: The Bank Account Transfer. In a simplistic model, we might have a table of bank accounts in our database, and a second table of deposits. Constraints will guarantee that we can't do things like creating a deposit for an account that doesn't exist, but they won't ensure that if we create a deposit of X in one account, we must also create a corresponding deposit of -X for some other account so we aren't inventing or destroying money. Of course, in a more realistic implementation, you'd never represent transfers between accounts as two separate stages like that, and if there is any single place to look up deposits into specific accounts it's probably some sort of view or cache rather than the authoritative data. But rather like going from single-entry to double-entry bookkeeping, to avoid relying on something higher up our system to ensure consistency, we've had to restructure our whole data model and the schema implementing it. In a sense, this is still just normalising the data, but that's a bit like saying implementing a word processor is, in a sense, just refactoring and extending a text editor.
- blmsjw 6y ago
  I think as long you keep use stored procedures as atomic operations/transactions it both simplifies code and improves performance. In your example, a transfer money procedure should handle both the balance increment and decrement of all accounts involved in a transaction before returning. Things start to get hard to maintain if you have stored procedures that only complete a portion of the business logic and rely on code in the business logic to “understand” what still needs to be done
- couchand 6y ago
  A related principle is to make illegal states unrepresentable. If you can possibly insert a debit and fail to insert the corresponding credit, thereby making money magically appear, the schema is wrong. This isn't a matter of just insufficiently normalized, it simply doesn't represent what you claim it represents. The database shouldn't be viewed as just some kind of scratch pad where you write stuff down so the application server doesn't have to remember it. If the database is the source of truth, then it is the model, and the application is simply a view.
  - libraryofbabel 6y ago
    This is a good principle to aim for but it is impossible to fulfill in many cases. There are always going to be invariants that the application may wish to enforce which cannot be enforced in the database (or, if there was some ideal schema to enforce them, the migration from the existing schema would be enormously costly). This is the whole reason we have ACID transactions in relational databases - so the application can enforce its invariants! (Exercise for the readers: construct a set of invariants that cannot be enforced within a database schema.)
    - Silhouette 6y ago
      This is a good principle to aim for but it is impossible to fulfill in many cases. There are always going to be invariants that the application may wish to enforce which cannot be enforced in the database As HN doesn't show moderations, I will just say that this is exactly the point I would have made if I'd been replying first. Relational databases are good at enforcing the kinds of relations provided by basic set manipulations. If you try really hard, you can encode some more complicated constraints, but as the complexity increases it becomes unwieldy and eventually impractical to enforce everything at schema level. Edit: Changed "database level" to "schema level" to clarify that I'm not talking about stored procedures here.

Silhouette 6y ago

The fact that a developer can "corrupt" the database with non-atomic stuff is an hint to me that the database probably doesn't have the right referential integrity constraints in place and probably is not normalized either. The relational model is built out of the box to keep a consistent state of the data and this is one of its main value propositions. This is true when interpreted the right way, but I don't think real world problems are always so tidy. Let's consider Standard Toy Example #17: The Bank Account Transfer. In a simplistic model, we might have a table of bank accounts in our database, and a second table of deposits. Constraints will guarantee that we can't do things like creating a deposit for an account that doesn't exist, but they won't ensure that if we create a deposit of X in one account, we must also create a corresponding deposit of -X for some other account so we aren't inventing or destroying money. Of course, in a more realistic implementation, you'd never represent transfers between accounts as two separate stages like that, and if there is any single place to look up deposits into specific accounts it's probably some sort of view or cache rather than the authoritative data. But rather like going from single-entry to double-entry bookkeeping, to avoid relying on something higher up our system to ensure consistency, we've had to restructure our whole data model and the schema implementing it. In a sense, this is still just normalising the data, but that's a bit like saying implementing a word processor is, in a sense, just refactoring and extending a text editor.

blmsjw 6y ago
I think as long you keep use stored procedures as atomic operations/transactions it both simplifies code and improves performance. In your example, a transfer money procedure should handle both the balance increment and decrement of all accounts involved in a transaction before returning. Things start to get hard to maintain if you have stored procedures that only complete a portion of the business logic and rely on code in the business logic to “understand” what still needs to be done
couchand 6y ago
A related principle is to make illegal states unrepresentable. If you can possibly insert a debit and fail to insert the corresponding credit, thereby making money magically appear, the schema is wrong. This isn't a matter of just insufficiently normalized, it simply doesn't represent what you claim it represents. The database shouldn't be viewed as just some kind of scratch pad where you write stuff down so the application server doesn't have to remember it. If the database is the source of truth, then it is the model, and the application is simply a view.
- libraryofbabel 6y ago
  This is a good principle to aim for but it is impossible to fulfill in many cases. There are always going to be invariants that the application may wish to enforce which cannot be enforced in the database (or, if there was some ideal schema to enforce them, the migration from the existing schema would be enormously costly). This is the whole reason we have ACID transactions in relational databases - so the application can enforce its invariants! (Exercise for the readers: construct a set of invariants that cannot be enforced within a database schema.)
  - Silhouette 6y ago
    This is a good principle to aim for but it is impossible to fulfill in many cases. There are always going to be invariants that the application may wish to enforce which cannot be enforced in the database As HN doesn't show moderations, I will just say that this is exactly the point I would have made if I'd been replying first. Relational databases are good at enforcing the kinds of relations provided by basic set manipulations. If you try really hard, you can encode some more complicated constraints, but as the complexity increases it becomes unwieldy and eventually impractical to enforce everything at schema level. Edit: Changed "database level" to "schema level" to clarify that I'm not talking about stored procedures here.