On 20 May 2001, at 0:23, Robert F. Tulloch wrote:

> This problem originated from a credibility issue. Members of this
> "association" were
> being assigned Id numbers which just kept getting bigger and bigger.
> This association was started in 1974 and it had active member id's
> like: 1, 73, 122, 123, 287, ...... All the missing numbers were
> members who cancelled or moved to Jakarta or Miami. Just kidding.
> Anyway, these folks wanted to know why they just couldn't have 1,2,3
> etc, etc. The explanations fell on deaf ears.
>
> > The only way I got something like this to work was using a Generator
> > to populate the primary key and another one as the
> > "reference-name-key"(wierd name, but I believe that it illustrates
> > the use).
> >
> > It worked like this, when populating the tables at the user entry
> > level, a generator was used to give me the primary key (ex: 10),
> > after all the fields and whatever else was ok, and only when I
> > confirmed that it was going to be a valid entry, I fetched a number
> > from the "reference key generator". The only part that is annoying
> > is that when the users wanted to find something, I had to fetch the
> > master record to find out what is value of the original
>
> That is exactly what most everyone in the BCB Interbase NG told me
> to do. It seemed
> like an unnecessary duplication of effort to first get a gen_id key
> then look up the next sequential real Id. Seemed only logical to use
> the real Id as the key. They said I couldn't do it that way and it
> would cause all sorts of lock conflicts and duplicate key problems. No
> such problems in testing it.
>

There are various methodologies, anything that re-uses primary key
numbers, will always at some point collapse. Usually when you start
using the primary key as a foreign key for another table. You either
need to make perfect cascading rules, and lose the historical record,
or you may get some historical items attached to the wrong member.

Say for example, you start selling stuff to members, like high
quality Swiss watches (made in Taiwan), with the association logo on
the face. For legal reasons you need to keep the sales records for
several years because the government tells you to. However the sales
record refers to the member number who bought the watch. That member
leaves, and you reassign the member number to a new member, now your
database has a problem, it shows that you sold the new member a
watch, before he became a member.

To solve the first problem, you use two numbers, one visible, one
invisible. The invisible number, never gets reused, former members
get marked as inactive, and left in the database, until you can
legally delete all of the detail records connected to them. Now here
is a solution for your reuse problem. When you delete a member, you
don't delete them, you mark them as inactive, leaving their visible
member number in place. Inside a stored procedure, you have a select
where you get the first inactive member record, where the member
number is not 0, grab the number, and then set the inactive members
number to 0. You return the number to the application. This all
takes place inside a transaction, if the new member add fails, you
roll back the transaction, which restores the number to the inactive
member. If there are no inactive members, then you call a generator
to give you a new number.

BY a second token, a member who became inactive, can be reinstated,
but they may or may not have the same member number.

Paul





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