0

On our prod db we have two tables:

  1. companies
  2. invoices

enter image description here

Relationship: A company has many invoices An invoice belong to a company

The invoices table contains duplicate invoice_number for each company. What is the best approach to make invoice number unique for each company. what ever approach we go with, the changes shouldn't affect the existing references to the invoice table. It should only affect new invoice records.

  • Make it an auto-increment column with a starting value higher than the maximum number that is already in the column. – Dave Apr 16 at 10:47
  • 3
    How about using the invoice id as invoice_number going forward? Since it is unique it will also automatically be unique per customer. – JacquesB Apr 16 at 10:49
  • Is invoice_number actually referenced by other tables? Are you sure the id isn't the thing being referenced at the database level, and then invoice_number being a search term for users? – Greg Burghardt Apr 16 at 11:03
  • 1
    What to the duplicates mean? Like id 2&3 are both company 1 and invoice 002, with different dollar amount. What does that mean? – Erik Eidt Apr 16 at 16:08
4

My answer is not what you expect, but I have to make it.

In most countries around the world, the invoice number has to be unique, but only within the emitting company.

In some countries, invoice numbers are strongly regulated (especially with regard to sequentiality and absence of unused numbers between consecutive invoices). In some other countries it is not, but in case of a tax control, the controller will be very suspicious about holes within one company.

This is why it is completely normal to have duplicates if you only look at the invoice numbers. Uniqueness is only to be guaranteed for a pair of company_id and invoice_number. So in reports, you either need to display both with the appropriate sorting, or ask the user for which company the report is.

If you want to work internally with a globally unique number, just use your existing id field and let it be populated automatically by your db.

Another approach can be used for ensuring unique numbers globally: use different number ranges for different companies. This is usually well understood by tax controllers, as long as numbers are still sequential within one range. A variant thereof is to implicitly determine the number range based on the company_id (e.g. company_id * 1000000 + sequential counter). Then you need to ensure that no number range is overflown. (Again, tax controllers usually understand if there's one break in the numbering if the numbering scheme is changed and the change is well documented).

  • I'm agreeing with everything said here. Just an addition: invoice numbers usually don't have to be mere numbers but may also contain other characters. So something like company_id + "/" + sequential counter could also be an option. – Thomas Schremser Apr 17 at 9:35
1

The correct approach for you here is that the Primary Key for the Invoices table is the combination of id and company_id. Both form a unique identifier, and there is no problem for it to work like that. If you need to provide an Id in your UI (or to the client), you can just show something like '4-10' ( {company_id}-{id} ).

But! If you still need a unique identifier in a column, then add a new one in the Invoices table and call it something like unique_id. You don't mention any other dependencies, but this way you'll prevent breaking anything that might be currently using the id or company_id combo. Plus, what Christophe mentions is correct: some countries need correlative numbers for invoices or proof of payment.

I do not recommend replacing the current ids: You might lose data integrity and/or other relationships you might have (your clients might be using the ids in their systems, for example). Plus: it would be a cumbersome, rather hard and dangerous task to execute on your production database.

1

As I see it, you have several solutions. It all depends on whether you want a permanent one or a workaround.

A permanent one would be to update your invoice table and replace the field invoice_number with something like:

CONCAT(company_id, ‘-’,  invoice_number)

Now, if you want a workaround, you might change your data selection query to something like this:

SELECT
    c.name AS company,
    CONCAT(i.company_id, ‘-’,  i.invoice_number) AS  invoice_number,
    i.total_amount
FROM
    invoices AS i
LEFT JOIN
    companies AS c
ON
    c.id = i.company_id

Or you could just create a view where you have company_id and invoice_number already concatenated in your invoice_number field and point your queries to that view.

Hope this is useful.

0

Perhaps just concatinating the company_id into what the plain invoice number would have been. Depending on how many invoices your system assigns to a given company, you might want to add another 0 or two to the original invoice number if you need to keep it in a numeric format.

For example,

  • The next invoice for company id #1 is 100003
  • The next invoice for company id #5 is 500004

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.