In the blade form I get the latest/highest number from the database and add it by 1 when creating new data:

<input type="number" min="0"  name="ir_number" placeholder="IR Number"
   value="{{ DB::table('cases_reports')->get('ir_number', $report->ir_number)->max('ir_number')   1 }}"/>

It's working as it should, but how can I specify to fill up the gaps between numbers first?

e.g. 1 - 5. create 2, 3, 4 first before creating 6

Also, sometimes the user deletes a record, how can I also fill that deleted number then continue counting?

CodePudding user response：

It's not recommended to reuse identifiers, primary auto_increment or not. That value should represent the unique identity a single entity and recycling values will inevitably cause conflicts/confusion.

Consider your scenario for invoicing. Someone generates an invoice which is assigned a non database derived ir_number of 001. A few more invoices are generated and now you're at 005. The invoice with ir_number 003 is deleted, but that invoice has been generated and so someone has an invoice with that ir_number.

A new invoice is generated and your application determines that 003 is available for reuse and so assigns this new invoice an ir_number of 003. You now have two invoices with the same ir_number. So if someone has a concern with invoice 003, which one is it?

Additionally, consider the scenario where two invoices are being generated simultaneously. This could result in a deadlock/race condition trying to retreive or use the next available ir_number. You could do some validation and/or additional checks before and during creation, but that is additional work.

Consider what it is you're attempting to achieve, does it really make sense to reuse ir_number?