3 ms·

I love SQL for making it possible to almost trivially enforce most business rules. Such as: You can only use one of the in another table specified values in thi

I love SQL for making it possible to almost trivially enforce most business rules. Such as: You can only use one of the in another table specified values in this field.

lmm 6y ago
I struggle to understand this viewpoint. In my experience business rules are harder to express in SQL than in practically any first-class programming language. "Is this value one of this list of values" - whether that list is hardcoded or dynamically obtained - is completely trivial. (Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "database" may be done at will in production then you'll find SQL logic easier to adjust, but that's a reflection of your policies rather than any fundamental reality.)
- Akronymus 6y ago
  With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
  - lmm 6y ago
    > With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Which is to say that you have little control over deployment. Adding a new foreign key constraint might block queries for an indeterminate amount of time while a new index is built, but you have no way to introspect or predict that at the SQL level (if you're lucky there might be a specific way to find out via the internals of your particular datastore implementation). PHP fans used to tout being able to just edit the code on the production server as an advantage, but most of us recognise it as the opposite. > Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points. If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
    - Akronymus 6y ago
      We use exclusively stored procedures, so if we want to add a FK constraint while the application doesn't adhere to it, we do one of two things: Either add it to the application and push out a new version. Or handle it within the stored procedure. Additionally, there would be the option of updating the database when pushing a new software update out. > If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens. I think I formulated it badly before. In this case I meant that we have one stored procedure for each thing we do. So that, if the underlying data changes for example, we only need to change the procedures directly acessing it. We can then call those procedures from multiple different parts of the code. And the procedures are all managed within one application, mssms in our case, where you can have a much cleaner structure than within code. In my experience, in PHP for example, you tend to write VERY similar/same queries in multiple locations, which can cause you to find it harder to all instances.
- PeterisP 6y ago
  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.
scotty79 6y ago
It makes some things simple and insanely fast at runtime, at the cost of making other things impossible or if possible horribly awkward and just as slow as in any other programming language.
- Akronymus 6y ago
  In those rare cases, you can do post processing on the data in code if need be. I personally don't see it fully as either/or but as mostly SQL with enhancing the data in rare cases via "traditional" code.

I love SQL for making it possible to almost trivially enforce most business rules. Such as: You can only use one of the in another table specified values in this field.

lmm 6y ago

I struggle to understand this viewpoint. In my experience business rules are harder to express in SQL than in practically any first-class programming language. "Is this value one of this list of values" - whether that list is hardcoded or dynamically obtained - is completely trivial. (Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "database" may be done at will in production then you'll find SQL logic easier to adjust, but that's a reflection of your policies rather than any fundamental reality.)

Akronymus 6y ago
With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
- lmm 6y ago
  > With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint. Which is to say that you have little control over deployment. Adding a new foreign key constraint might block queries for an indeterminate amount of time while a new index is built, but you have no way to introspect or predict that at the SQL level (if you're lucky there might be a specific way to find out via the internals of your particular datastore implementation). PHP fans used to tout being able to just edit the code on the production server as an advantage, but most of us recognise it as the opposite. > Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points. If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
  - Akronymus 6y ago
    We use exclusively stored procedures, so if we want to add a FK constraint while the application doesn't adhere to it, we do one of two things: Either add it to the application and push out a new version. Or handle it within the stored procedure. Additionally, there would be the option of updating the database when pushing a new software update out. > If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens. I think I formulated it badly before. In this case I meant that we have one stored procedure for each thing we do. So that, if the underlying data changes for example, we only need to change the procedures directly acessing it. We can then call those procedures from multiple different parts of the code. And the procedures are all managed within one application, mssms in our case, where you can have a much cleaner structure than within code. In my experience, in PHP for example, you tend to write VERY similar/same queries in multiple locations, which can cause you to find it harder to all instances.
PeterisP 6y ago
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.

scotty79 6y ago

It makes some things simple and insanely fast at runtime, at the cost of making other things impossible or if possible horribly awkward and just as slow as in any other programming language.

Akronymus 6y ago
In those rare cases, you can do post processing on the data in code if need be. I personally don't see it fully as either/or but as mostly SQL with enhancing the data in rare cases via "traditional" code.