Re: remove gaps in a table
Date: Wed, 20 Dec 2017 19:20:09 +0100
Message-ID: <f9vnupFh5u2U1_at_mid.individual.net>
On 12/20/17 13:28, mireero wrote:
> On 12/20/2017 12:07 PM, Gordon Burditt wrote:>>> I'm looking for a way to "reduce" the order_number, to remove gaps, that
>>> - order_id is unique
>>> - order_number is unique for a given month and start back at 1 every
>>> month.
>>> - there are gaps in the order_id sequence, for example, in october, we
>>> have 4 orders with order_number: 1, 2, 3, 5.
>>> In november, we have 6 orders with order_number: 1, 2, 4, 9, 10.
>>>
>>>
>>> is to obtain:
>>
>> It seems to me that the point of an order_id is so the customer and
>> your company can use it to refer to a specific order in communications
>> between them. In this case, every time you change an order_id, you
>> have to inform the customer. Customers won't put up with that for
>> very long.
>>
>> If you think about it, it gets even worse with bank account numbers
>> where you have transactions in transit with bank account numbers
>> on them, and the consequences of having a transaction get attached
>> to the wrong account are much more serious.
>>
>> It's not at all uncommon for a change in a database to have or need
>> real-world consequences.
>>
> In this case, the order_id is the really unique and everlasting > identifier of an order. > The order_number is only an interface with the client. > I agree that it shouldn't change. > But I have gaps...
At work we have a PSP who only register successful translations, this makes it really difficult to to figure out what went wrong and you need to get in touch with someone who can look at the logs and from there try to figure out the rejection reason and sometimes you happen to have a similar tracking number as another of the PSP's customers and you get some information which is unrelated to the the transaction you tried to make.
[Quoted] [Quoted] For you if you want completely gapless order numbers, don't delete, when [Quoted] an order is "deleted", you generate a new order which is the complete opposite of the original order, so if the original order is for 3 nails of a value of 3 dollars, then the new one is for -3 nails for the value of -3 dollars. This way you have gapless order numbers for the accounting department and the numbers would be correct from an accounting point of view.
-- //AhoReceived on Wed Dec 20 2017 - 19:20:09 CET