Re: remove gaps in a table
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Wed, 20 Dec 2017 18:23:02 +0000
Message-ID: <p1e9q7$k8m$1_at_dont-email.me>
>
> Thanks for all the answers.
>
> I do get the idea, don't store the order_number in the database.
> It's seducing (by the way, I need to be sure that no order will be
> deleted in the future).
>
> But still, if the order_number is not stored, how would I fetch an order
> just knowing the order_number (and the month) ?
Date: Wed, 20 Dec 2017 18:23:02 +0000
Message-ID: <p1e9q7$k8m$1_at_dont-email.me>
On 20/12/17 00:01, mireero wrote:
> On 12/20/2017 12:29 AM, J�rgen Exner wrote:
>> On Tue, 19 Dec 2017 22:03:16 +0100, mireero <mireero_at_free.fr> wrote in >> comp.databases.mysql: >> >> [...] >>> I'm looking for a way to "reduce" the order_number, to remove gaps, that >>> is to obtain: >> [...] >>> I guess we could just iterate on all rows and set the order_number, >>> beginning from 1 but I have the feeling I'm missing something. >>> >>> Note: in the real table, there are thousands of rows. >> >> Are you sure this is a good idea? I just imagine a customer calling: >> >> "Good morning. This is ACME Corporation customer support. How may I help >> you". >> "Good morning. We have an issue with our last order. The order number is >> 12345". >> "Just a second, let me look this up. Ah, yes, I see. What is the problem >> with the 50 anal beads?" >> "Anal beads? This is the Jesuit Convent of xyz and I am calling about >> the order for 30 rosaries!". >> >> jue >>
>
> Thanks for all the answers.
>
> I do get the idea, don't store the order_number in the database.
> It's seducing (by the way, I need to be sure that no order will be
> deleted in the future).
>
> But still, if the order_number is not stored, how would I fetch an order
> just knowing the order_number (and the month) ?
It is not a question of not storing it, it is a question of never altering it once stored.
Once a transaction has been committed, you CANNOT change it, in business. You may issue a reversal - i.e. a credit note. but you cannopt change and order NUMBER. You might amend details
Ots the sam,e with part numbers in a materials stiore. New part numbers are always issued - old ones are never re-used.
How many times have you seen 'KP 756747 superseded by part number XF 4571086' and so on?
-- There’s a mighty big difference between good, sound reasons and reasons that sound good. Burton Hillis (William Vaughn, American columnist)Received on Wed Dec 20 2017 - 19:23:02 CET