Subject Re: [ib-support] Question about koncept
Author Jörg Schiemann
> On Wednesday 07 February 2001 04:48, you wrote:

> > 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
> 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.
> example you could fairly easily change the CUSTOMER_ID to be alpha-numeric
> 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
> choice. His "Relational Database Writings 1991-1994" has a section on
> surrogates and he refers to another book called "Data Base Management"
> 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
> tables and for tables which represent 1 to many relationships.

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

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

I can imagine that.


Jörg Schiemann