Re: remove gaps in a table
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Wed, 20 Dec 2017 08:33:44 -0500
Message-ID: <p1dorm$j64$1_at_jstuckle.eternal-september.org>
>
> Actually, this order_number is the public identifier of the order.
> It is the number shown in the invoice.
> I made a mistake, it rolls back to 1 every year, not every month but the
> idea is the same.
> I don't know exactly why there shouldn't be any gaps but anyway that's
> the specification I got.
> For some accounting reasons, I guess.
> So, if I don't store it in DB, and some code somewhere tries to retrieve
> a row just knowing the order_number and the date, I guess I have to play
> with LIMIT.
>
> Note: my new spec is to fill the gaps with some today's orders, and to
> avoid deleting orders from now on.
> I'm not sure of the implications with the dates, though... (maybe none,
> as far as I do it before next year).
Date: Wed, 20 Dec 2017 08:33:44 -0500
Message-ID: <p1dorm$j64$1_at_jstuckle.eternal-september.org>
On 12/20/2017 6:01 AM, mireero wrote:
> On 12/20/2017 02:13 AM, J�rgen Exner wrote:
>> On Wed, 20 Dec 2017 12:02:36 +1100, Geoff Muldoon >> <geoff.muldoon_at_trap.gmail.com> wrote in comp.databases.mysql: >> >>> In article <3gaj3dd0dm04len4iejom72dg8otbrkdo5_at_4ax.com>, >>> jurgenex_at_hotmail.com says... >>>> >>>> On Wed, 20 Dec 2017 01:01:40 +0100, mireero <mireero_at_free.fr> wrote in >>>> comp.databases.mysql: >>>> >>>>> 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!". >>>>> >>>>> Thanks for all the answers. >>>>> >>>>> I do get the idea, don't store the order_number in the database. >>>> >>>> Au contraire. Typically the order number is _THE_ unique identifier >>>> around which everything else revolves. So is must be stored in the DB. >>> >>> My reading of the original post is that there were TWO fields: >>> order_id - autoincrement primary key >>> order_number - per month no-gaps business counter, not globally unique >> >> Well, ok, if that's only a "business counter" we are talking about then >> there is no reason to store it in the DB and a lot of good reasons to >> not store it in the DB. >> >> jue >>
>
> Actually, this order_number is the public identifier of the order.
> It is the number shown in the invoice.
> I made a mistake, it rolls back to 1 every year, not every month but the
> idea is the same.
> I don't know exactly why there shouldn't be any gaps but anyway that's
> the specification I got.
> For some accounting reasons, I guess.
> So, if I don't store it in DB, and some code somewhere tries to retrieve
> a row just knowing the order_number and the date, I guess I have to play
> with LIMIT.
>
> Note: my new spec is to fill the gaps with some today's orders, and to
> avoid deleting orders from now on.
> I'm not sure of the implications with the dates, though... (maybe none,
> as far as I do it before next year).
You still won't be able to guarantee no gaps. It is entirely possible for an order to be backed out for one reason or another after the order number has been assigned and more orders having been entered. Even if you use sequentially numbered paper forms you will get gaps due to voids, damaged forms and similar reasons.
The public number itself should not be of importance, other than the fact it is unique within the year. Customer's don't care what their order number is, as long as it retrieves their order.
I think you need to go back and discuss the specifications and why they are insisting on it being in order with no gaps.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Wed Dec 20 2017 - 14:33:44 CET