RE: Database design question

A counter (weak) example:

Two years ago a 6 char unique ID field was approaching 899999 and I had to
come
up with a solution for an impending overflow.

Increasing the size of the field would have cause great pains
(this ID is used as foreign key in hundreds of tables)

Since it is a char field that was incremented by digits only (000001,
000002, 000003...)
I've come up with the idea to start incrementing it using all characters
(and not digits only) when the left most char of the ID reaches 9.

(899999, 9AAAAA, 9AAAAB, 9AAAAC.....9AAAA0,..)

I've sent a help call on this forum and sure enough got a piece of code to
do it.
(I forgot who the persons were but Thank them all)

We bought ourselves some 40 years before the next overflow...

See you guys in 40 years then

Gad




I'll give you an example. Unique customer id. It was deemed that customer
id's were only required to be up to 6 characters in length. However, we are
now reaching 9999999 customers, no one ever thought you would get that many
customers! They print customer id on hundreds of documents, and it is well
known what it is by many units in the organization.

That key (6 character column) was used throughout hundreds of files. We now
need to increase the length. What do you do? In RPG that means changing
hundreds of RPG programs most likely and recompiling. In .NET we don't need
to do anything, a string is a string, we don't care how long it is
typically so .NET shields you from some of those types of changes.

Now, if all those hundreds of files were using surrogate integer (or better
yet 64bit integer) single primary keys, we could instead increase the
length on the customer id field (the 6 character one) in only ONE table.
All the other tables are already using a surrogate key that can grow to
trillions in length to link back to that one customer record.

This rule basically applies to anything where you need to change a data
type in a table, and it is used as a reference in other tables to join. You
cause yourself a lot of development pain when not using surrogate keys for
everything because invariably someone always thinks of a reason to change a
human readable key such as an order #, customer #, payment #, etc for one
odd case or another due to an error in processing, a data modernization
effort, or many other reasons and surrogate keys will shield you from a lot
of those changes.