At 09:52 PM 05-11-02 +0000, you wrote:

>Hi,
>
>A few days ago, I had a visit from mr. consultant again :-)
>He analyzed the database and made a report of it, here are the
>remarks that I would like to have a second opinion of.
>
>1) Don't use triggers and stored procedures, all the DB logic should
>exist in your application ?
>
>For me I thought good designers use the full power of the DBMS
>engine ?

You are both right. For strictly client/server oriented design, where
there is not a requirement to make your system portable to other back-ends,
your principle is correct. In a heterogeneous database environment, or
where there is an intention to move databases onto another RDBMS before the
end of life of your application code, his principle is correct.

By your principle, you get a stable, well-performing system with the
capability to optimise performance and fully protect data integrity for the
life of your system.

By his principle, you get a generic, under-performing system that is
vulnerable to every bad or inconsistent implementation that a client
application can throw at it.

In the middle, you have the server-based application server, a layer where
all of the business rules are implemented. Client applications communicate
with only this layer, not with the database itself. This n-tier approach
solves a lot of the risks inherent in your consultant's approach and makes
the business rules independent of the RDBMS. It still denies much of the
performance, optimization and integrity that you get from triggers, stored
procedures and server-managed referential integrity.

Rather than accept the consultant's black-and-white assessment, you should
be asking him to consider *your* customer's current and future
requirements. If performance, permanence and integrity matter more than
portability, your principle might be the better case.

>2) A primary key should not have any meaning related to the data in
>the table. So, that means all primary keys of all tables should be
>build up out of generators.
>
>The primary key of my customers table is the customer no. Is that
>wrong ??

From the point of view of *database theory*, it is functionally correct,
as long as the customer no. is unique.

From every other point of view, it is wrong. Your consultant is correct
to say that primary keys should not have any meaning related to the data -
the term for this characteristic is "atomic".

Consider, for example, if your customer one day decides it needs to use a
different format for customer numbers...

I know you watch the IBO list. Check back there to yesterday's postings,
for a response I posted to a question from Frank Ingermann about "ACID
rules". The subject thread was "Re: [IBO] Stored procedure, ExecSQL and
Suspend". I posted a reprint of a very comprehensible description of ACID
rules given to me by Ann a few months ago, which she had picked up from
another open source site.

cheers,
heLen