Best practice for Invoice Prefix in MySQL?

Question

I'm designing a schema for invoicing and I'm trying to decide on the best practice for carrying out the following requirements. There are multiple companies which have multiple branches. Each branch will want its own auto-increment number independent of the other branches (and companies). On top of that, there are different classes of invoices. I have set up the following table structure

tbl_invoices

tbl_branches

|branch_id|branch_prefix|

tbl_classes

|class_id|class_prefix|

I am strongly favouring INNODB because of FKs and transactions. However, I have a problem with auto-increment (MyISAM would auto-increment multiple column primary keys easily). Another issue I have is having to join multiple tables all the time when I pull the invoice data because I need the full invoice_id that concats the different prefixes. I've also ran into the issue of what happens when the prefix strings change in the future. It's probably unwarranted for the past issued invoices to also have their prefixes change. I'm thinking I should add an additional prefix column for tbl_invoices which will store the full concat prefix on insert. I'm not too sure if this is the right practice? I've considered storing the entire invoice_id in full concat form in the column but I wouldn't be able to enforce uniqueness for INV-0001 from INV-00001.

This would still mean I have difficulty with the auto-increment.

@tadman you obviously never had to work on an OLAP application :) — Shadow, Mar 28 '17 at 19:53
These are arbitrary "numbers" with arbitrary restrictions - I'd say, don't use them as primary row identifiers at all. Let normal auto-increment ids do that job; and use custom logic - either outside the database, or embedded via a stored procedure - to produce the correct invoice numbers satisfying all requirements, and store them separately, to join them with the auto-increment main order id. — CBroe, Mar 28 '17 at 19:56
Possible duplicate of [How auto-increment within a subset of the table MYSQL](http://stackoverflow.com/questions/10879104/how-auto-increment-within-a-subset-of-the-table-mysql) — Shadow, Mar 28 '17 at 20:15
@tadman there are situations where you do not need transactions, mvcc, and all these advanced transactional features. — Shadow, Mar 28 '17 at 20:16
@tadman then why did **you** start with a completely irrelevant rant against myisam? — Shadow, Mar 28 '17 at 20:30
@CBroe That's an interesting proposition. branch_id, class_id, company_id are all AI primary keys for their respective tables. They're only referenced under the invoice table. Are you suggesting using this method? "I'm thinking I should add an additional prefix column for tbl_invoices which will store the full concat prefix on insert" — Joshua, Mar 28 '17 at 20:39
@tadman it is still an irrelevant rant against myisam, which has its uses. Make a point using correct logic and avoid boisterous claims. — Shadow, Mar 28 '17 at 20:40
@CBroe I'd still need to store the numeric portion of the invoice_id somewhere so that I can increment it manually. How do you suggest doing this? I've also read that InnoDB would take the first UNIQUE multicolumn index as a clustered index so a key with all the columns should function as quickly as an organic single INT AI column? Could you elaborate on why you still suggest using normal AI? — Joshua, Mar 28 '17 at 20:46
@tadman if pointing out inaccuracies in irrelevant and boisterous comments is fighting, and not helping, then yes, I'm here to fight. Again, your last comment is incorrect. — Shadow, Mar 28 '17 at 23:53
@Shadow Explain how MyISAM is in *any way* relevant to solving this problem. — tadman, Mar 28 '17 at 23:58
@tadman if you do not think that myisam is relevant to this question, then again, why did you make your first comment? Btw, as the OP has also explained, myisam supports auto_increment field as a secondary field in a primary key and this feature offers a solution to this question. Innodb does not support this feature. Therefore myisam is very relevant to this question. Would I choose myisam for an invoicing system? No. Is your comment saying that it "isn't worth considering for anything" correct? Absolutely not. — Shadow, Mar 29 '17 at 00:07
@Shadow "Would I choose myisam for an invoicing system? No." Thank you. That's all I'm saying. — tadman, Mar 29 '17 at 00:12
@tadman no that was not all you were saying. You conveniently skipped over your ridiculous claims that myisam "isn't worth considering for anything". — Shadow, Mar 29 '17 at 00:13
@Shadow I've been burned by it too many times to give it any love. Sorry. It's one of the worst database engines out there. — tadman, Mar 29 '17 at 00:15

score 0 · Answer 1 · answered Mar 28 '17 at 20:23

If you have low insertion volume and are willing to tolerate a little friction you can set a UNIQUE index on your invoice numbers. If they're formatted properly, that is they will sort correctly because they're of form INV-00001 instead of INV-1, INV-2, etc. then you can do this:

SELECT MAX(ident) FROM invoices WHERE ident LIKE 'INV-%'

Then try and insert with that incremented by one. This is an optimistic approach, that is you're expecting it will probably work, and if not, you'll get an error about an index conflict and you can increment and try again until it succeeds.

A pessimistic approach, where you're expecting it to fail, involves transactions and an increment table where you track the increments for each prefix. You'll need to do some quick locking here to avoid creating a lot of contention. This system is a lot more complicated to get working flawlessly, and if done incorrectly can lead to ugly lock problems.

I'd go with the optimistic approach until you have a specific reason why it won't work.

Best practice for Invoice Prefix in MySQL?

1 Answers1