George,
> I have general design question about Postgres usage: How does one decide
> how much, and what parts of logic should go in DB rules, triggers,
> functions, constraints etc, versus what should go in the application?
Ideally, this should be done strictly on the basis of carefully planned design
architecture, where each business rule is implemented at the application
level where it is most effective, such as referential data integrity in the
database and security in the middleware.
Realistically, business logic tends to be implemented in the layer where you
have the most programming expertise. The people on this list are often
expert DBAs, so they implement as much business logic as possible in the
database in the form of views, triggers, and rules. However, I"ve seen
plenty of shops with multiple crackerjack Java programmers and a weak DBA
implementing all of their business logic in the middleware.
> The main thing not done yet is to facilitate ad-hoc queries
> (via odbc excel etc.) from db-naive users: maybe restructuring the
> db to make it simpler, maybe views and functions... The data is
> somewhat complex in structure.
This desired feature would argue strongly in favor of putting as much business
logic as possibly in your database in the form of views and rules. If users
can bypass the interface and middleware, you cannot rely on it to enforce
data integrity and access control.
--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco
