Re: Big Picture - Mailing list pgsql-novice

Erik,

> Is "putting as much of your application logic into SQL" the preferred
> way to develop DB-driven applications?  I ask this because the only
> database I've used is MySQL, which I enjoyed learning about and using
> but required me to implement a lot of the DB logic in my application
> code.  (It was a PHP app.)  That's why I want to try PostgreSQL, to
> get more experience with writing logic into the database itself.  But
> I was curious if there are any resources that discuss why this
> methodology is preferrable if indeed it is.

Application design:

1-tier application (example: MS Access)
Data, interface, and business logic in one layer.

2-tier applications
A. MySQL/PHP
Interface & Business Logic -- PHP
Data -- MySQL

B. PostgreSQL/PHP
Interface --- PHP
Data & Business Logic -- PostgreSQL

3-tier applications
(example: Java/CORBA distributed app)
Interface: Java/Swing
Business Logic: Java EJB
Data: PostgreSQL

N-tier applications are like the 3-tier, only they keep adding
object-oriented layers in the Business Logic portion.

3-tier, or N-tier, applications, are superior programming because they
offer abstraction and encapsulation of business logic, which smaller
models cannot accomodate.   Also, 3-to-N-tier applications are far
better at handling data sources and interfaces distributed across
several machines or even several networks.

However, the more tiers you add, the higher the initial programming
time and development cost of the application.   Frequently, the n-tier
approach is simply overkill for the client's business needs and budget.

Of the two 2-tier models, then, I argue strongly that the
PostgreSQL/PHP model, with the business logic in the database, is
superior, for the following reasons:
1. Language:  PostgreSQL functions can make use of SQL, PL/pgSQL, Perl,
and C, using each language for what it is best at.  Particularly, SQL
and PL/pgSQL have the advantage of performing set operations far faster
than procedural or OO programming languages -- and many business rules
require set operations.
2. Data consistency:  It is simply easier to manage perfect data
normalization from the database end of things, where triggers and rules
can be expected to apply universally, than from interface-level code,
where unanticipated exceptions in the code can turn into inconsistent
data.  Particularly with browser-based interfaces, client sessions may
be interrupted at any time, so it's hard to depend on interface code
for consistency.
3. Maintainence:  Close integration of interface display code with
business logic (the MySQL/PHP model) is high-maintainence because often
changes to the interface display cause bugs in the business logic code.
4. Security:  If your entire business logic is in PHP, there is no way
to secure or limit access to the database from an attacker who gains
control of the web server.   If the PHP code is constrained by
database-side business logic, however, it is much easier to limit the
damage even a priveleged attacker can do through database security.
5. Portability: If your interface contains little or no business logic,
then it is far faster to replicate it on a new platform because there
is simply less code on the interface side of things.   For example, if
you had a PHP interface which you wanted to replicate using C on the
Palm, it would be considerably less work to do so if all you need worry
about were data layout and forms, rather than security, normalization,
and concurrent edits as well.

Persuasive?

-Josh Berkus