In order for me to create unique invoice numbers for my customers, do I :-
Lock database tables and increment the invoice number
Try and queue requests on the server side
Do I need to use Petersons or Dekkers algorithm to enforce mutual exclusion, avoid deadlock and starvation
Even a timestamp won’t work if two users access at exactly the same time.
I can create a unique invoice number from SessionID’s but this requires cookies, what if they are disabled.?
This is a serious problem for online payments as PHP processes are not able to talk to each other,
other than setting a unique flag field in a database, but won’t that cause performance issues.
How do the big companies do it. ???
Please only respond if you have significant knowledge in Computer Science and Web dev.
thankyou so much, I am using mysql so I will try that, I feel like a bit of a fool for asking such a simple question and making
it so complicated, but the way I have designed the system, I was trying to make the invoice number or customer number a combination of meaningful sequences, I guess I could just auto increment and append the result to my invoice or customer number
I recently read an interesting article. It does talk about PostgreSQL and the example is not invoice numbers but account balances. It may be of use to you.
thankyou, I will try that, any articles or offerrings you have I will follow up as I have been foiled by this problem for weeks and have tried work arounds that I’m just not sure of.
I don’t really understand this question.
Don’t you make an insert query already? If so, why you’re asking?
If not, what’s the use for such an invoice number without the actual invoice?
Would it be worth thinking perhaps that the business might have other ways of creating invoices apart from the website and that there should be a way of consolidating them, not just relying on autoincrement and perhaps as OP said better making a sequence of meaningful data?
Perhaps I have gone about the design in the wrong manner ( I really don’t know ), what I was trying to do
was to read the database table, increment the invoice/reciept or customer number (or all of the above),
append a timestamp and some other information such as customer type and other metadata and then write
it back to the database as a new customer or new invoice/reciept etc. So what I was trying to do
was to create a unique number that provided information in itself at a glance, and that was where I was
running into trouble.
Either the non-web-generated invoices are in the same database table, in which case auto-increment will work for that scenario, or they are in a different database / table / system, in which case there will need to be a merging routine of some sort, and that merging routine can deal with potential clashes. Several options spring to mind - prefixing invoice numbers with a code to denote web or non-web, renumbering on import but retaining the original web invoice number as a cross-reference so the customer can refer to it if required.
If there are web and non-web invoices, it’s basically the same problem you have with any multi-location database, for example creating a unique customer ID on a system where data replicates across multiple locations, and all locations can create new customer IDs. You can’t use an incrementing number on its own for that, because checking the other systems during creation takes too long, or might not even be possible if some of the other databases aren’t available.
So you will run into a concurrency issue with this setup for sure.
I still don’t understand why don’t you use the auto-incremented id which is guaranteed to be unique, and append it with whatever info you wish.
What’s the problem to concatenate several fields from the same data row to make your desired invoice number format, adding id, type and registration date at display time?
Thanks, I was trying to avoid using merging routines and the complexities involved in non-web-generated customer numbers/receipts/invoices etc. I was hoping to centralise in one database ( redundancy/backup issues aside ) and I wanted to reserve an receipt number in my database before processing the credit card payment through my provider, with a one-to-one correspondence between the records in my system and those of my provider.
Well, that would work then - use the auto-increment field, and just discard any if the payment doesn’t go through. I think some sites don’t give the reference number to the customer until the payment is processed, which gets around the issue of missing numbers in the sequence.
If you want to give them a reference before the payment is done and you don’t want to have holes in the sequence if payment fails, then you’d need to be giving the customer an order reference, then when the invoice is produced after the payment is processed, link the invoice number to the order internally, and let the customer use the order reference. Will there always be one order with one invoice for it, or could there be a single order with more than one invoice, or an invoice that consolidates more than one order?
But for a single centralised database, the auto-increment field seems to be the best way. After all, even if you have fifty customers on the web site at any one time and it appears they’re all concurrent, they’re almost certainly not actually concurrent as far as the processor / storage system is concerned. And even if they were, MySQL will deal with that for you.
Thanks, I think you are right, I discarded the auto-increment option because I was hoping to make my primary / foreign key as descriptive as possible rather than a simple incremental number, (to use as Customer Number etc). I understand your point about concatenating serveral fields at display time. I guess my database access mode will effectively be via the uniquely auto generated field which is the counter of number of customers as the primary key to my tables, and that I should ditch the idea of an aggregated identifier that I was formerly generating in PHP from session identifiers. In fact if I kept the aggregated identifier I would have two primary keys for the one record which is probably a bit tardy, perhaps you can advise. ( I was using PHP session identifiers quite extensively in my login / authentication modules, I don’t know if I will have to redesign because it was built around my PHP generated customer numbers and associated email addresses ).
Thanks for your response, I think you are right, I have to let MySQL deal with the concurrency issues, because if I code to factor in contingencies it’s going to be a world of heartache as I have already attempted a first version but testing it and ensuring that it is bullet proof under load is extremely difficult to accomplish.
I have made a few systems where I needed to generate unique invoice numbers and in none of the cases could I use auto-increment for that simply because the business requirements for an invoice number are always different. In most cases an invoice number comprised of some form of date like year and month, plus some kind of symbol and a sequential number that has to reset itself every month or every year and sometimes there are different types of invoices and each type needs a separate sequence counter. That’s very far from what an auto-increment can offer.
I used various approaches to handle concurrency. One of them was simply locking a table for reading and writing, generating a next sequence number in php based on the current highest number in the table, inserting the invoice and unlocking the table.
Another approach I used without locking the table uses detection of errors when inserting an invoice and re-trying a few times with new invoice numbers in case of an error. Let’s say I store invoice number in the inv_no column and I must set it as unique in the database. My invoice number needs to be in format INV/[year]/[month]-[sequence_within_year], for example INV/2017/04-280. And in the table I store the sequence number (280) in a separate column invoice_counter. Some sample code:
$i = 1;
$now = new DateTime;
$year = $now->format('Y');
$month = $now->format('m');
do {
// get next invoice counter:
$invoice_counter = $db->fetchColumn("SELECT MAX(invoice_counter) FROM invoices
WHERE invoice_date BETWEEN '$year-01-01 00:00:00' AND '$year-12-31 23:59:59'") + 1;
// create invoice number according to required custom pattern:
$inv_no = "INV/$year/$month-$invoice_counter";
try {
// try to insert invoice into db, inv_no needs to be a unique key:
$db->query("INSERT INTO invoices (inv_no, invoice_counter)
VALUES('$inv_no', $invoice_counter)");
break; // success
} catch (PDOException $ex) {
if ($i > 20) {
throw new Exception("Can't create a valid invoice number - too many retries.");
}
}
$i++;
} while (true);
I simply fetch the next available sequence number from the database, create the number and try to insert. If another process inserts an invoice in this very short time span with the number I have just generated, the database will raise the unique constraint violation and I will catch it and retry the whole process. I set an arbitrary limit of 20 just for emergency sake, I think it is enormously extremely unlikely a running system would ever reach that limit (even retrying once would be a very rare occurrence)
Of course, a separate counter column is not strictly necessary because you can extract the number from the invoice number string. But sometimes extracting parts of string can be awkward in mysql, that’s why I use a separate column for the number. BTW, Postrgres has a much better choice of string functions and there I don’t use the separate column because any string extraction is quite easy.
I would just like to thank everyone for responding, I am still struggling, but making small in roads, I am using the session PHP_SESS_ID to help ensure uniqueness and using an INSERTwith this as my unique id, and allowing MYSQL to gen an autoincrement