On 5 Nov 2002 at 21:52, Nico Callewaert 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 ?

Okay, I think you have about 10 opinions by now, so I'll make it 11. I think the
consultant isn't worth much. For several reasons, I'll ennumerate.

1) It makes sense to put the business logic into the database, using triggers, views
and stored procedures, because as you write additional applications, you don't need
to implement all of the business logic over and over. This is especially the case
where one app is in C++, another is in Delphi and another is in Python or Perl.

2) Putting all of the logic in the app, means you need to implement it all over the
network, so if you are reading a large dataset and updating a large dataset, rather
then using 5GB of network space, you do it in an SP and leave it as "local" logic on
the server.

3) Which is cheaper a $10,000 server and 25 $1,000 workstations, or a $2500
server and 25 $2,500 workstations? Enough said.

> 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 ??
>

It's not wrong, it's just more work to implement, because you need logic to ensure
uniqueness, where as a surogate primary key (built from a generator), is always
going to be unique. What I always do is add a field, this field is the tablename_ID
(i.e. CUSTOMER_ID) this field is created from a generator and is the primary key.
Other tables use this primary key field for the foreign key. The reasoning is that
suppose you have a customer number 666, and one day the customer company is
sold to someone who considers the number 666 is extremely bad luck, now you, as
a nice caring company, are willing to change it to another number 777 (which is
considered a lucky number), so now you need to find all the occurances of 666 and
change it to 777, and create a big mess because you missed a table, this is because
you were told about it at 4:45PM on the Friday of a long weekend, and it has to be
done before you leave....

Sure the customer's CUSTOMER_ID might be 666, but what he doesn't know can't
hurt you....

Now I refered to it as a foreign key, this isn't always implemented as a true foreign
key, but the term fits the usage.