Re: Are stored procedures/triggers common in your industry

On 4/20/22 15:18, Guyren Howe wrote:
> I’ve really only ever worked in web development. 90+% of web
> developers regard doing anything at all clever in the database with
> suspicion.
>
> I’m considering working on a book about implementing business logic in
> Postgres, and I’m curious about how common that actually is.

Well, there are 2 schools of thought:

1. Put the business logic into the application
2. Put the business logic into the database

Putting the business logic into the application can give you more
flexibility around enforcing them. On the other hand, you also increase
chances of inconsistency. There will likely be more than one application
using reference tables like ADDRESS, ZIP_CODE, STATE, COUNTRY, QUARTER,
ACCOUNT, CUSTOMER and similar. If there is a rule that a country must
exist before you add an address in that country into the table, that can
be enforced by a foreign key. Enforcing it within the application does 2
things:

1. Move the rule code to the application server which is traditionally
weaker than a database server. In other words, you are more likely
to run out of CPU juice and memory on an application server than you
are likely to run out of resources on the DB server.
2. There is a possibility for inconsistency. Different applications can
use different business rules for the same set of tables. That means
that data entered by one application may make the table internally
inconsistent for another application.

I am a big proponent of using foreign keys, check constraints and
triggers to enforce business rules. I am also a big proponent of
avoiding NULL values wherever possible. Database design is an art. CAD
software used to be popular once upon a time, in a galaxy far, far
away.  Properly enforcing the business rules in the database itself
makes the application more clear and easier to write.

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com