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

<SNIP>

> > 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).

I'm not sure that we share the same opinion about using the fields.
The users only see the field CUSTOMER_NO which is unique and for me the
surrogate key
The other filed CUSTOMER_ID is invisible for the users.
If you did share the same opinion, I don't understand your sentence. You
maybe have an example?

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

Thanks, I'll look if it's still available.

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

Why don't you use surrogate keys for tables which represent 1 to many
relationships?

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

I can imagine that.

Thanks

Jörg Schiemann