I am stuck with this design requirement:

In an inventory of tens of thousands of items, we designed the unique ID of each item to be auto-increment (MySQL) - serial in Informix. The requirements have been extended to be able to also assign the ID from a batches of reserved numbers. This is a valid user requirement allowing the use of pre-printed labels on some items.

You can, of course, enter and ID into an auto-increment field, providing it does not exist, but it alters the next auto increment value. That, in turn, causes "strange" sequencing in the database.

Our ID is integer.

Anyone with any experience in doing this?

Thanx

My experience always says: auto incrementing primary keys have no values. Therefore it should not matter a toss if there are "gaps" in your sequencing

Yes, that is a valid concept. Would you then suggest we have a separate field for the actual inventory number or just number inventory in a random-incremental fashion?

i would just number inventory items in a unique fashion