How to achieve Unique, Sequential Numbers Reliably?

Need help Database
34

@mishav: yeah, what you propose will at times NOT work.

For anyone still confused on this:

When we try to measure the number of things that have already been given a serial number, that measure will SOMETIMES be wrong with respect to the current item we are trying to assign a unique serial number to.

This is because, at any moment, the result of Do a search for all orders, sorted by creation date looks like this:

[ {orders that have serial numbers} {orders not yet assigned serial numbers but that are created before our current workflow order} { OUR WORKFLOW ORDER } {orders not yet assigned serial numbers created after our current workflow item} ]

Now, in many cases, {orders not yet assigned serial numbers but created before…} and {orders not yet assigned numbers created after…} are both zero.

BUT, when they are not, our attempts to measure the number of items serialized will fail. Here are the scenarios:

  1. Search for all orders looks like: [ {orders with serial numbers} {OUR ORDER} ]
    This is what many folks are assuming the list looks like all the time. In this case, yeah, a simple analysis will work. We can do one of two ways: OUR ORDER’s serial number should be set to the last serialized order’s serial number +1, OR we can just say “oh, OUR ORDER is at the end of the list, so it’s serial number should be set to the List’s:count (the length of the list)”. But the list does not look like this all the time.

  2. Search for all orders looks like: [ {orders with serial numbers} {some number of orders with earlier creation dates but not yet serialized} {OUR ORDER} ]
    In this case, if we set OUR ORDER’s serial number to the number of orders with serial numbers +1, we will get the wrong answer. However, if we set OUR ORDER’s serial number to the list’s count, we will be correct. But…

  3. Search for all orders looks like: [ {orders with serial numbers} {some number of orders with earlier creation dates but not yet serialized} {OUR ORDER} {some number of orders with later creation dates but not yet serialized}]
    In this case, BOTH simple methods of assigning the serial number will fail. The number of orders with serial numbers +1 is not the correct serial number for OUR ORDER, and the :count of the list is not the correct serial number.

However, in ALL of the above scenarios, the correct serial number for OUR ORDER is the 1-based index of OUR ORDER in the list.

3 Likes