Re: Wich the best way to control the logic of a web application? - Mailing list pgsql-general

On Aug 19, 2009, at 9:46 AM, Andre Lopes wrote:
> What do you think aboout this? Should I mix logic in Database and
> PHP or should I control the logic only in the PHP?

There are no real hard and fast rules, of course, just rules of
thumb.  Here are some of mine.

First, remember that there is rarely "an application" for a database.
One of the most useful parts of a database is that it provides a
central repository for data across multiple applications.  For now,
the web interface might be the only way to get at the data, but live
systems tend to grow clients.  Soon, you have external processes
handling data interchange with other companies, processes sending
customers email...

This means two things:

1. You want to make sure that the database maintains as much data
integrity as it can, as long as that data integrity really must be
enforced across all applications.  For example, in an inventory
database, if an item's inventory level *always* equals all inventory
receivings minus all shipments, then that's a good candidate for a
rule enforced by a trigger in the database.

2. On the other hand, be careful of business logic that isn't
universal creeping into the database.  For example, for the web
application, you might send an email to a user when they register.
But do you *really* want to do *exactly the same thing* on a bulk load
of new users from an external source?

In other examples, page-to-page flow is probably not a great candidate
for encoding in the database; I would think that it makes far more
sense for the database to store the state of the various business
objects, and let the PHP application decide what to display to the
user.  Similarly, formatting is often a better idea in PHP, since you
may have more information about the right kind of formatting.  (Kind
of a shame, since PostgreSQL's data type text formatting is in many
ways superior to PHP's!)

However, a business rule that is designed to prevent bad data from
entering the database is a good candidate from being enforced in the
database.

Lastly, as a matter of preference, I don't like putting things into
the database that can block for extended periods, like sending email.
I think those are better put into external processes that run against
the database.  (And, of course, putting things that can block for an
extended period into the web application isn't good either.)

Here's one concrete example of a decision I made recently; of course,
I may have made the wrong one. :)  Customers on this side can search
across a large number of different types of entities, including
catalog items, catalog categories, product buying guides, articles,
etc.  The design required that these be presented in particular ways,
separate one from the other.  I could have implemented a procedure in
the database which took the search and returned the results, but I
decided that would be pushing too much of the UI display use case into
what should be a data store.  Instead, the application does separate
queries for each type, and unifies the results.  (This does have a
negative performance characteristic, since the application has to make
multiple trips to the database instead of calling one function, but it
wasn't significant enough to be a problem.)

Hope this helps!
--
-- Christophe Pettus
    xof@thebuild.com