Auto numbering and deleting records

[Raater]

Hi

We have a job management system. Each new record that's created is given an auto-entered serial number. At the moment if a job is cancelled during entry or delted we lose the job number forever - giving us gaps in our number sequence. If a user creates a new job I want to be able to let them either cancel the entry or delete a record but not delete/lose the job number. Is there anyway of ensuring that when a record is deleted the auto numbering does not move on to the next number when a new record is created?

CHeers

Rich

[Vaughan]

You don't want to go here. Learn to live with gaps in the numbers.

You can create a system whereby deleted numbers are stored in a table. New records first look to the table for recycled serial numbers, and only create new serial numbers if none can be recycled.

The downside with serial number recycling is that it's possible to get the numbers out of sequence: user A creates record 43, user B creates 44, user A deletes 43. The next record in the database will be 43, not 45. The sequence will be 42, 44, 43 45.

Would the gap-haters be happy with randomly ordered serial numbers?

Of course, such serial numbers should NEVER be used as primary keys for relationships.