Re: Are stored procedures/triggers common in your industry - Mailing list pgsql-general

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