[thelist] [SQL] Selecting next available number

[Juha Suni]

Les Lytollis wrote:
> I need to supply a unique id for display on screen at the *start* of
> an order process. The id should be the next available within a range,
> eg 1234000000 to 1234999999.

snip

> If I add the id to the db at the end of the process, how do I ensure
> that the same id is not generated again (for a different customer)
> before it is added to the db? Eg customer 1 is given id 111, customer
> 2 starts an order before customer 1 has completed, so Max(id) is still
> 110.
>
> If I add the id to the db at the start of the process, how do I select
> those id's from abandoned transactions that have "gone back into the
> pool"?

Rather than trying to tackle this clearly logical problem the technical way,
I'd still suggest you think through the logic once again.

Im not sure why you need to present the unique id to the customer at the
beginning, I'll just assume that you have to, and this unique id is of some
value to the customer, or he can use it some way. In my opinion its very
simple. If you give the customer an id, for it to be of any use, it must
already point out to some existing data/order/whatever. Some data/order/item
must already be created and set. This is very logical. Something must be
created first, then give the customer the pointer to it, otherwise the
pointer is of no value. So create id and other necessary data at the start
of the process.

The problem you seem to be afraid of that empty, unused id's will be left in
the database. Is this really a problem? If it is, I would suggest perhaps
using two tables, and differentiating order-id's given to customer from
unique id's of the database, and linking them with another table. Create the
order-id for the customer at the start in a customer-table (which you can
also use to store data while the order forms are filled), and create the
unique-id to the real ordertable and the linking of the two at the end of
the process. Then if customers need to contact you with their order-id
(which I assume is the reason you give them the id at the start), you can
link it to the unique-id and get their order-data. Yet, your real table with
orders still has unique id's with no "empty" fields left behind.

This would distinct "transactions in progress" from "completed tansactions"
and link them, while keeping the "completed transactions" table solid.

HTH

-- 
Suni