4 ms·

Also in my experience DB Schemas often (but not always) outlast logic implemented in applications. The more the business assumptions reside in the database, the

Also in my experience DB Schemas often (but not always) outlast logic implemented in applications. The more the business assumptions reside in the database, the easier it becomes to rewrite applications on top of it later.

sorokod 7y ago
Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting.
- krn 7y ago
  > Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting. The trick is to keep declarative business logic in the database layer, and imperative business logic in the application layer. This allows a large team of developers to move quickly without breaking things.
  - wiz21c 7y ago
    that's very well put. Thx!
  - victor106 7y ago
    Very Interesting. Never heard this before. Can you give some examples of what would constitute declarative business logic vs imperative business logic?
    - krn 7y ago
      Declarative programming expresses the logic of a computation without describing its control flow. This new feature in PostgreSQL is a great example of that: generated columns (declarative logic) were introduced to reduce the need for triggers (imperative logic). In SQL, declarative logic consists of constraints, indexes, views, and prepared statements. They can significantly increase the efficiency and reliability of the entire system. Imperative logic is mostly triggers and stored procedures, both of which can become hard to maintain and scale.
  - sorokod 7y ago
    How does one logically follows from the other?
- pnako 7y ago
  Clearly the feature should not be abused, but calculated columns are great for stuff that's obvious and always true (i.e. independent from applications), e.g. a trade value computed from a trade unit price and a trade quantity. This way I can select the top N trades for a given key without having to do the computation in the application, or storing redundant information in the DB.
  - mcny 7y ago
    Not postgresql but I saw a start date and an age in days columns in MS SQL server. The age gets updated daily. It didn't sound right to me. I'm pretty sure I'd fail my database class in college if I did that. What is different in real life and why didn't they teach me this in college?
    - bobwaycott 7y ago
      I obviously can't answer for this particular case, but my first thought on why I would consider doing such a thing is if I had an app in which number of days old was something that had to be queried, displayed, and/or used in other functions/queries a massive number of times per day in the course of normal application usage. If the application had low usage, or number of days old was infrequently queried/displayed/used, I wouldn't consider it. The moment I found that significant time & resources were spent calculating the value in normal/regular usage, I'd start looking at ways to reduce that time & resource usage. How to go about it varies, but the win of that value being immediately available without computation could mean a lot to an app/business and its users.

sorokod 7y ago

Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting.

krn 7y ago
> Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting. The trick is to keep declarative business logic in the database layer, and imperative business logic in the application layer. This allows a large team of developers to move quickly without breaking things.
- wiz21c 7y ago
  that's very well put. Thx!
- victor106 7y ago
  Very Interesting. Never heard this before. Can you give some examples of what would constitute declarative business logic vs imperative business logic?
  - krn 7y ago
    Declarative programming expresses the logic of a computation without describing its control flow. This new feature in PostgreSQL is a great example of that: generated columns (declarative logic) were introduced to reduce the need for triggers (imperative logic). In SQL, declarative logic consists of constraints, indexes, views, and prepared statements. They can significantly increase the efficiency and reliability of the entire system. Imperative logic is mostly triggers and stored procedures, both of which can become hard to maintain and scale.
- sorokod 7y ago
  How does one logically follows from the other?
pnako 7y ago
Clearly the feature should not be abused, but calculated columns are great for stuff that's obvious and always true (i.e. independent from applications), e.g. a trade value computed from a trade unit price and a trade quantity. This way I can select the top N trades for a given key without having to do the computation in the application, or storing redundant information in the DB.
- mcny 7y ago
  Not postgresql but I saw a start date and an age in days columns in MS SQL server. The age gets updated daily. It didn't sound right to me. I'm pretty sure I'd fail my database class in college if I did that. What is different in real life and why didn't they teach me this in college?
  - bobwaycott 7y ago
    I obviously can't answer for this particular case, but my first thought on why I would consider doing such a thing is if I had an app in which number of days old was something that had to be queried, displayed, and/or used in other functions/queries a massive number of times per day in the course of normal application usage. If the application had low usage, or number of days old was infrequently queried/displayed/used, I wouldn't consider it. The moment I found that significant time & resources were spent calculating the value in normal/regular usage, I'd start looking at ways to reduce that time & resource usage. How to go about it varies, but the win of that value being immediately available without computation could mean a lot to an app/business and its users.