--- In firebird-support@yahoogroups.com, toldy007@... wrote:

>
> Dear All,
>
> I need your help with an issue. I'm writing a program using Firebird

that will be able to print

> invoices.
>
> Invoices in my country (and probably in other countries too) have to

have continual numbering.

> 2007/1, 2007/2, 2007/3 etc.
> There can't be any gaps between numbers!
>
> The question is, if there are multiple people creating new

invoinces, how can I guarantee that

> there won't be any gaps in the numbers?
>
> There are two issues I'm facing:
> 1, I can't use generators, because a potential rollback operation

would cause gaps in invoice

> numbering. Also, I'm planning to store the invoices in the same

table for different years, so there

> will be an invoice with the same ID for year 2007, 2008, 2009, 2010 etc.
>
> 2, If I don't use generators I run into another problem. I'm storing

the invoice data in multiple

> tables like
>
> INSERT INTO Invoices (Year, InvoiceID, ...) VALUES(2007, :id,...)
> and then
> INSERT INTO InvoiceItems(InvoiceID, ItemID, ...) VALUES ...
> Transaction.Commit;
>
> If I use InvoiceID = (SELECT MAX(InvoiceID) + 1 FROM Invoices WHERE

Year = 2007) to get the

> new invoice's ID, then if there are multiple users creating

invoices, a potential

> Transaction.Rollback could cause gaps in the number!
>
> -> User 1 calls the inserts, gets ID = 1
> -> User 2 calls the inserts, gets ID = 2
> -> User 1 rolls back
> -> User 2 commits
>
> The result is a gap! ID = 1 remains unused!
>
> In such situations, User 2 should get the ID = 1!
> Any ideas on how to solve this problem?

I wonder if your problem is as big as you make out. You seem to be of
the opinion that the requirement is that every invoice is sequential?
If that is *really* the case, then you will need to completely
serialise the invoice generation, possibly even to the point where the
invoice is physically printed before the next person can create one.
It may work in a small office with a small handful of people
generating invoices, but I can't begin to imagine the bottlenecks in a
large company.

Why I ask whether it is really the case is that I want you to consider
whether the requirement is for all invoices to be 'sequential without
gaps', or whether the requirement is for 'no unexplained gaps'. You
will no doubt need professional advice from an accountant or some
other government authority.

The IBO website has a document explaining one way of achieving an
unbroken sequence. Basically it is a mechanism to have a reusable
number pool. When issuing an invoice, you first attempt to use a
number from this pool before generating a new one. If you cancel for
any reason, the number goes back to the pool. To me
(IANAA[ccountant]), this method is worse for auditing. An invoice
generated at 9am may have a higher number than one generated at 11am.
It gives you the 'without gaps' but you lose the 'sequential'.

Canceled invoices are a part of everyday life. Someone else has
already billed it, or there is an addition to the order prior to the
goods being shipped. I doubt the concept would be very interesting to
an auditor. What I would find important is that I can pick any invoice
number and be able to trace what happened to it. But I don't know your
laws, so get some advice before making any decision.

And as Helen said, don't use your invoice numbers as the primary key.
Create a surrogate field.

Adam