0

I am designing a system where I have multiple shops where each shop should have its own set of sequential numbers for its invoices. Obviously my primary ID column will be sequential for all invoices in the system so obviously I will need another column to store this "shop specific" invoice number. What is the best manner to store and get the next ID for this shop specific number? For example would it be safe to simply get it right from the invoices table doing something like: SELECT MAX(INV_NUM) FROM INVOICES WHERE SHOP_ID = # and add one, and subsequently create the new invoice record? Or would there be issues with the timing if 2 instances of my script were to run at the same time? For example the first script fetches the next ID, but before it gets the chance to create the new invoice record the second instance requests the next ID and gets the same one as the first script... I was then thinking about just storing the last used number in a separate table and as soon as I request the next invoice number immediately write the new next order number and persist it so that the time between fetching the next order number and creating the record that the next request would rely on is kept to an absolute minimum... literally 3 lines of code: 

$nextId = $shop->getLastId() + 1;
$shop->setLastId($nextId);
$em->persist();

Invoices

------------------------------
| ID  |  INV_NUM  |  SHOP_ID |
------------------------------
| 1   |     99    |     1    |
| 2   |     100   |     2    |
| 3   |     100   |     1    |

Shops

-------------------
| ID  |  LAST_ID  |
-------------------
|  1  |     100   |
|  2  |     100   |
snoop168
  • 394
  • 3
  • 16
  • Here is one option if you are using MyISAM: http://stackoverflow.com/questions/677542/auto-increment-by-group – Tim Biegeleisen Dec 08 '16 at 02:13
  • You would normally use a trigger for this. – Gordon Linoff Dec 08 '16 at 02:14
  • @GordonLinoff But how could he use a trigger on the same table? – Tim Biegeleisen Dec 08 '16 at 02:16
  • For InnoDB, you must do it in a transaction. Does symphony know about such? – Rick James Dec 08 '16 at 04:48
  • So do you guys think I should get the next invoice number from the invoice table itself or track separately in the "shops" table? Would it matter? Doing in the shops table adds an extra spot where something could go wrong. I just thought there would be less delay for me to get increment and write as quick as possible. I'm guessing this link is what I need to be looking into http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/transactions-and-concurrency.html as if you're not familiar with symfony it includes doctrine2 as the ORM. – snoop168 Dec 08 '16 at 11:57

1 Answers1

1

If you're using Doctrine, which I assume you are since you're using Symfony then you can lifecycle events to listen for changes in your entities. Before saving you can then update your second column to the incremented value.

Regarding race conditions, to be sure you don't have bad data in your database you can put a unique constraint on your shop ID and invoice number.

mickadoo
  • 3,337
  • 1
  • 25
  • 38