Advice on strategy to get next sequential number

Hi all,
I am updating an application that has a routine to get the next
sequential number. This
number is used as a client number, so everytime that a client is created
this routine
gets the next available number. The algorithm that gets the client
number goes some
thing like:
get next available number from system table
do
do a select on client table where clientnumber = new client number
if client with client number exists increment client number by 1
loop until no client with new client number is found
update system table with new client number + 1
this somewhat odd routine was/is necessary because the previous
application
that was in place segmented the client number into categories, something
like
(not accurate, for example only)
client number range industry
10000 - 20000 private sector
30000 - 40000 education
60000 - 70000 finance
The new application does not do this, instead it uses a system table
that
contains the client number that will be used. Since there are thousands
of clients
that already have client numbers that are segmented, the client number
that
is next must be tested to make sure that it does not exist already.
Is there a better algorithm/strategy that I can follow than the one
listed
above? Any suggestions are welcomed.
Thank you for your time
SagaYou have only 60,000 possible client numbers, so you can pre-allocate
them:
CREATE TABLE ClientNumbers
(client_nbr INTEGER NOT NULL PRIMARY KEY,
client_type CHAR(3) NOT NULL,
CONSTRAINT valid_client_type
CHECK (client_type IN ('pri', 'fin', 'edu')),
CONSTRAINT valid_client_nbr
CHECK (CASE WHEN client_type = 'pri'
AND client_nr BETWEEN 10000 AND 20000
THEN 'T'
WHEN client_type = 'fin'
AND client_nr BETWEEN 30000 AND 40000
THEN 'T'
WHEN client_type = 'edu'
AND client_nr BETWEEN 60000 AND 70000
THEN 'T' ELSE 'F' = 'T' END
allocation_date DATETIME DEFAULT CURRENT_TIMESTAMP, -- null =
available
<< other allocation data>>
);
Now update the existing rows when you allocate a new number.
UPDATE ClientNumbers
SET allocation_date = COALESCE (@.my_date, CURRENT_TIMESTAMP),
<<other data>> = <<new info>>
WHERE allocation_date
= (SELECT MIN(client_nbr)
FROM ClientNumbers
WHERE client_type = @.my_type
AND allocation_date IS NULL);
I would prefer to have a check digit in this scheme. I also do not
like that it does not allow for growth. You might want to also to get a
copy of my SQL PROGRAMMING STYLE book for some help with designing data
encoding schemes.|||Thank you for your response. You are right, such a scheme never
allows for growth, that is why that application was changed to another
that simply increments the last client number and attempts to use the
new number. This application; however, has a problem that it sometimes
creates duplicate client numbers. I looked into this and discovered that
there is a second routiine that has the same function located elsewhere
in the app, so I am going to correct this. I was wondering how I could
clean up the mess the first app left behind, or better worded, which
would
be the better way to deal with the segmented cleint numbers. The
algorithm
that is in place now works, as far as assigning a new client number
(dupes
not withstanding), but I was woneering whwther there is a better, more
efficientway of doing it.
I'll give one example. There are at least 1,000 clients begining with
30000.
Once the counter gets to this number it will have to increment manually
a
few thousand times in order to get to the next available number!!!
Arrgh!
I can't see myself leaving this algorithm as is, but will if I don't see
a better
way of doing it <g>.
Your solution is ingenious! Personally I would apply it. It is far
better to
have a specific column to indicate the client type. The only problem I
have
is that I can't create a new field in the table at this time without
modifying
some of the app so that it will support the new field. In any case, I
also
believe that the users no longer care about the numbering scheme and its
respective categories.
Again thanks for your input.
Saga
"--CELKO--" <jcelko212NON@.SPAMMEearthlink.net> wrote in message
news:1138837029.731710.111760@.f14g2000cwb.googlegroups.com...
> You have only 60,000 possible client numbers, so you can pre-allocate
> them:
> CREATE TABLE ClientNumbers
> (client_nbr INTEGER NOT NULL PRIMARY KEY,
> client_type CHAR(3) NOT NULL,
> CONSTRAINT valid_client_type
> CHECK (client_type IN ('pri', 'fin', 'edu')),
> CONSTRAINT valid_client_nbr
> CHECK (CASE WHEN client_type = 'pri'
> AND client_nr BETWEEN 10000 AND 20000
> THEN 'T'
> WHEN client_type = 'fin'
> AND client_nr BETWEEN 30000 AND 40000
> THEN 'T'
> WHEN client_type = 'edu'
> AND client_nr BETWEEN 60000 AND 70000
> THEN 'T' ELSE 'F' = 'T' END
> allocation_date DATETIME DEFAULT CURRENT_TIMESTAMP, -- null =
> available
> << other allocation data>>
> );
> Now update the existing rows when you allocate a new number.
> UPDATE ClientNumbers
> SET allocation_date = COALESCE (@.my_date, CURRENT_TIMESTAMP),
> <<other data>> = <<new info>>
> WHERE allocation_date
> = (SELECT MIN(client_nbr)
> FROM ClientNumbers
> WHERE client_type = @.my_type
> AND allocation_date IS NULL);
> I would prefer to have a check digit in this scheme. I also do not
> like that it does not allow for growth. You might want to also to get
> a
> copy of my SQL PROGRAMMING STYLE book for some help with designing
> data
> encoding schemes.
>|||On Thu, 2 Feb 2006 09:50:16 -0600, Saga wrote:
(snip)
>I'll give one example. There are at least 1,000 clients begining with
>30000.
>Once the counter gets to this number it will have to increment manually
>a
>few thousand times in order to get to the next available number!!!
>Arrgh!
>I can't see myself leaving this algorithm as is, but will if I don't see
>a better
>way of doing it <g>.
Hi Saga,
Build a table with blocked ranges instead of just the used number. As
soon as the number generation routine hits the start of a blocked range,
set it to the end of that blocked range plus 1 (and periodically remove
blocked ranges that are lower than the current starting value, to
increase speed).
Hugo Kornelis, SQL Server MVP|||Interesting. Thanks for the idea!
Saga
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:nq05u1dotb9vakdnbuo8lbj47feh6h84ov@.
4ax.com...
> On Thu, 2 Feb 2006 09:50:16 -0600, Saga wrote:
> (snip)
> Hi Saga,
> Build a table with blocked ranges instead of just the used number. As
> soon as the number generation routine hits the start of a blocked
> range,
> set it to the end of that blocked range plus 1 (and periodically
> remove
> blocked ranges that are lower than the current starting value, to
> increase speed).
> --
> Hugo Kornelis, SQL Server MVP