0

In my app I have users and invoices (amongst other things).

I'm not sure what's the best way to make sure the invoices of every user start with 1 and go up incrementally. For example:

  • User 1 makes 2 invoices - ID 1 and ID 2
  • User 2 makes 3 invoices - ID 1 ID 2 and ID 3
  • User 1 makes 1 invoice - ID 3

I obviously can't use the default id column as it would just increase by one on every entry. The solution I thought about is having a new column in my invoices table called "incrementalId" that I update manually when I save the invoice and make it be 1 more than the maximum count.

Is this the standard way to go about solve this problem? An issue I can think of is that if I delete an invoice, the next one that I generate will most likely have an ID that is already taken. Or is deleting the entry when I delete the invoice not really the way to go?

Showner91
  • 115
  • 2
  • 11
  • In most databases, this requires a trigger. – Gordon Linoff Dec 10 '19 at 17:30
  • You should be able to use the [`IDENTITY`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15) property to auto-increment a field. You can find more [info here](http://www.sqltutorial.org/sql-auto-increment/) - just scroll down til you see '***SQL auto increment column in SQL Server***' – Matt Oestreich Dec 10 '19 at 18:07
  • @GordonLinoff do you mind expanding a bit more on that? – Showner91 Dec 10 '19 at 18:40
  • @MattOestreich I might be understanding this wrong but it just seems like a normal auto increment? Also, I'm using MySQL, it doesn't seen to have that property! – Showner91 Dec 10 '19 at 18:43
  • Oh my fault.. I misread your question... you want each user to have their own field that increments. Sorry about that. – Matt Oestreich Dec 10 '19 at 18:46
  • @MattOestreich that's correct. Do you have any suggestions? – Showner91 Dec 10 '19 at 19:03
  • @Showner91 I do not - it sounds like this is something you will have to do programmatically. – Matt Oestreich Dec 10 '19 at 20:22

1 Answers1

2

What you want to achieve is a gapless sequence in SQL. You cannot rely on auto generated ID as gaps can appear if a transaction is rollbacked (the id value is lost forever, getting it back is... something you should not want).

There are different ways to achieve that, it all depends on your requirements and if performance is a high parameter.

First choice, you just not populate your invoice number at creation. You just run a batch every x seconds or minutes or hours that fetch invoices that don't have number, get the highest invoice number from the database, and fill all these number columns in one transaction. The batch should be the unique actor to populate the invoice number column with no other concurrent execution (beware if the batch is still running to not spawn another). As you populate all empty invoice numbers in one shot, it is very performant, you don't need to lock the reading on the table as well, but it is not real-time.

Second choice, the real-time solution. You will have to use counter tables in your database to do so, it will surely involve some plsql or similar as your database is the only one to know the exact status of your invoice sequence number (multiple invoice creations can happen within your database, so the database is the only arbiter to decide this sequence). It will imply locking rows so if you upsert many invoices, every insert will slow down the whole thing, but you will get your invoice number ASAP. At the cost of delegating some work to your database to do.

Check this solution for postgresql for instance: PostgreSQL gapless sequences

zenbeni
  • 7,019
  • 3
  • 29
  • 60