On Wednesday 07 February 2001 04:48, you wrote:

> Hi,
>
> maybe it's a silly question, but in that case I'll take it.
>
> I want to be able to undelete and to see who deletes the post.
> After deleting a customer the customer_no should be available again.
> Which koncept schould I use?
>
> <SNIP>

> 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 ?
>
> Q2. Is there a better way?

I think a better way is to always use surrogate keys. Don't use the
CUSTOMER_ID as the primary key of the file but instead have an internal
primary key for the customer record (using a generator or whatever method you
want).

The benefits you get from this are:

1. Changing the CUSTOMER_ID doesn't cause any cascading updates.

2. Depending on your data requirements, you may be able to use just an
integer for the surrogate key which will reduce the space requirements in
tables that refer to the customers.

3. You are a little more isolated from changing customer requirements. For
example you could fairly easily change the CUSTOMER_ID to be alpha-numeric if
that was required down the road. You could just make the changes in the
customer table and wouldn't have to change every table that references the
customers.

> Q3. How do you handle deleting?

I do the same thing except I use a date field with the date the item was
deleted as an extra little bit of information.

> Q4. Is that all nonsense?

I don't think so! If you want to read more, writings by C.J. Date are a good
choice. His "Relational Database Writings 1991-1994" has a section on
surrogates and he refers to another book called "Data Base Management" which
I don't have.

PS: surrogate keys are a good idea but can be over-used. They are not the
best choice for all cases. Two places I don't use them are for simple lookup
tables and for tables which represent 1 to many relationships.

PPS: the use of surrogate keys gets you into debates and is somewhat like
discussing politics!

--
Brad Pepers
brad@...