Numbering invoices yearly and different structures with SQL and PHP

Question

I'm a beginner in SQL and I tried looking for an answer without any luck.

I would like to generate invoice numbers automatically with the following distinctions:

I have 2 different structures invoicing
I would like to avoid any numbering issue, for example not having a missing invoice number if something gets altered (ex: 1, 2, 4, 5, 8, 9 ...)
I would like them to be reset each year, so the numbering starts back to 1 every year for each structure :

ex: ENG_2022_1, ENG_2022_2 ... ENG_2023_1

ex: FR_2022_1, FR_2022_2, FR_2022_3, ... FR_2023_1

I have thought of setting a field with auto-increment, but that wouldn't work with the structure and year distinctions unless there's a workaround?

The other solution would be to get the year and structure before invoicing, and comparing it with a SQL MAX of the invoice number, before numbering but i'm not sure how good that solution?

Any help would be greatly appreciated

What DBMS are you using (or planning to use)? And am I correct to assume that you would like this number to be assigned when saving invoice to database? — markalex, Mar 26 '23 at 10:20
I think this is out of topic here. You should ask in Database Administrators — Rohit Gupta, Mar 26 '23 at 10:35
@markalex Correct assumption, and I'm using phpmyadmin - Server version: 5.7 — TheGix, Mar 26 '23 at 10:45
Terminology note: Phpmyadmin isn't a DBMS. It's a database administration tool, a front-end application (one of many such tools). It doesn't contain any data. Your DBMS is probably MySQL, by the sounds of it. That's the actual database server engine which stores the data. — ADyson, Mar 26 '23 at 12:43

KIKO Software · Accepted Answer · 2023-03-26T10:46:13.703

The use of an auto-increment column seems obvious, however, in your case you want to restart the numbering at the beginning of each year, so something should accommodate that.

I would create, at least, four columns for this in the invoice table:

invoice_id: This is the auto-increment column.
invoice_date: You can get the year with YEAR(invoice_date) from this date.
invoice_sequence: Either FR or ENG.
invoice_number: Contains the actual invoice number.

To create the invoice number you would need to know the number of invoices in a sequence for the current year. This could be retrieved in a separate query:

SELECT
  COUNT(*) AS invoice_count
FROM
  invoice_table
WHERE
  invoice_sequence = :sequence AND
  YEAR(invoice_date) = YEAR(:date);

In this query the :sequence and :date are placeholders for binding the values you want to look for.

After that you can insert the actual invoice. Here's a mock-up of such an insert:

INSERT INTO
  invoice_table
  (invoice_date,
   invoice_sequence,
   invoice_number,
   ......)
VALUES
  (:date,
   :sequence, 
   CONCAT(:sequence, '_', YEAR(:date), '_', :invoiceCount),
   ......)

In this query the :date, :sequence and :invoiceCount are placeholders for binding the values we already know. Note that in some cases you cannot repeat the same placeholder name. In that case use something like :name1 and :name2, or just ?.

Note that you could put the query to retrieve the count in the insert query as a sub-query. I didn't do that here for better readability.

score 1 · Answer 2 · answered Mar 26 '23 at 11:17

I would suggest you storing sequence, year, and number separately.

For this create three columns:

invoice_number   INTEGER,
invoice_year     INTEGER,
invoice_sequence VARCHAR(4)

And getting final value as

SELECT CONCAT(invoice_sequence, '_', invoice_year, '_', invoice_number) inv_full number
FROM MY_INVOICES

After that create trigger that will automatically generate invoice number on insert:

CREATE TRIGGER GENERATE_INVOICE_NUMBER
BEFORE INSERT
ON MY_INVOICES FOR EACH ROW
BEGIN
    SELECT IFNULL(MAX(invoice_number), 0) + 1
    INTO new.invoice_number
    FROM MY_INVOICES
    WHERE invoice_year = new.invoice_year
    AND invoice_sequence = new.invoice_sequence;
END

Here function MAX is used to get max value of previous number for specified sequence and year, to take into account possibility of invoice deletion and avoid violation of number uniqueness.

Numbering invoices yearly and different structures with SQL and PHP

2 Answers2