On 7 Feb 2001, at 12:48, Jörg Schiemann wrote:

To: "IB-Support" <ib-support@yahoogroups.com>
From: Jörg Schiemann <schimmi@...>
Date sent: Wed, 7 Feb 2001 12:48:12 +0100
Send reply to: ib-support@yahoogroups.com
Subject: [ib-support] Question about koncept

> Hi,
>

I usually try to avoid your scenario, because I prefer that the
primary key be non-user modifyable, for this very reason, it's a pain
in the butt to maintain the keys.


> The advantage I see with CUSTOMER_ID is, that the database don't need
> to a cascade update to all involved tables.
>
> Q1. The Question is, how much work is that for the database to do a
> cascade update, is it worth it to skip it with CUSTOMER_ID ?

Yes, the more complicated you make a design the easier it is for
something to go wrong.

>
> Q2. Is there a better way?

Yes, make CUSTOMER_ID an integer, create a generator to maintain it,
(use a trigger to handle this), then whenever a new customer is
created they get their very own key, any tables that refer to the
customer use CUSTOMER_ID as the key. This avoids having to update
secondary tables when the customer information is modified.

> Q3. How do you handle deleting?

I prefer to use the DELETED flag/purge program method, in this case
your program simply sets the DELETED flag, another program at some
future date, then does the actual deleting. This gives us several
advantages:

1) The delete seems to be instant.
2) You can have an un-delete (usually just pushing the delete button
again)
3) You can review the records to be deleted in a report
4) You can run a backup before the purge
5) You can put a lot of checking in the purge, because it can be run
when the computer is not otherwise terribly busy.

Paul

Paul Schmidt,
Tricat Technologies
Email: paul@...
Website: www.tricattechnologies.com