3

I've been thinking on this for a few hours, and I can't work out a viable solution - I can think of solutions, but they are clunky.

Using MySQL, I want to create an invoice table using auto increment for the invoice number. Pretty standard stuff. HOWEVER, my challenge is that I wish to have multiple companies generating invoices in this table, but all still with the unique invoice number that is auto incremented from the previous one.

For example:

  • Company A's previous invoice is 3010
  • Company B's previous invoice is 2144
  • Company C's previous invoice is 7889877

When Company A creates their next invoice I want it to be 3011. B to be 2145 etc.

I had the idea of doing a query WHERE company = 'B' ORDER BY id DESC then using PHP to increase that value++, then insert a new record with the invoice number inserted from PHP. In this case the 'id' field would be irrelevant to the actual invoice number, and just be a unique identifier.

Is there a better way?

|improve this question
2

That seems possible in MyISAM but afaik not using InnoDB:

CREATE TABLE `invoice_sequences` (
  `company_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`company_id`,`invoice_id`)
) ENGINE=MyISAM;

http://sqlfiddle.com/#!9/d135c/1 (if the fiddle returns an error, try again; it seems a bit slow sometimes).

|improve this answer
  • Yeah, this is great, I think you're right though, this only seems to work in MyISAM. But that isn't a problem... everything works perfectly - well as we would expect it to work...! Thanks – Ryan Oct 27 '15 at 12:37
  • Just one note - are you prepared for a possible gap in the sequence? auto_increment is supposed to be unique but does not guarantee "gaplessness". It can happen for number of reasons - error, rollback and insert ignore among others. – jkavalik Oct 27 '15 at 13:11
  • I hadn't actually considered this... any suggestions of where to start looking... articles or ideas/examples anywhere? – Ryan Oct 28 '15 at 8:46
  • Auto_increment is not the right tool for such a job. If you cannot tolerate gaps (which may be required for invoices numbering), you should probably implement some manual ways to do that - insert and invoice with ID of autoincrement - unique (in the entire table, not only for the company) and unchanging, and empty field for the invoice_number - and then have one service which "validates" invoice insertion by assigning it the proper number (by taking max() for a given company and adding 1 etc. - check this question and answers stackoverflow.com/q/14215894 – jkavalik Oct 28 '15 at 9:18
  • The MyISAM solution for invoice numbers does not suffer from rollback or insert ignore; errors can be handled by the app. So it may be a viable solution. (InnoDB does not have an equivalent.) mysql.rjweb.org/doc.php/myisam2innodb shows how to simulate the MyISAM feature, but does not guarantee gapless. – Rick James Dec 7 '15 at 6:37
0

Probably not. Auto incrementing attributes are typically based on a sequence object that just gives an incremental higher number each time it is called. That you're trying to do requires knowledge of the current state of the table, so there's unlikely to be a way other than querying the database first. You should of course wrap this in a transaction to keep it consistent.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.