3

Here is a theoretical scenario,

Suppose I have a client table and an invoice table. 1 client can have many invoices.

Now I want each invoice to have an invoice number that is unique to that client

i.e.

ClientId InvoiceNo
1        IN0001
2        IN0001
2        IN0002
2        IN0003
3        IN0001

Currently I am controlling this in my application by looking at max values etc but this is obviously not a great solution. I would much rather get my database to do this for me, as it should remove the risk of creating duplicate invoice numbers for a single client (race condition?)

I have been reading up on Sql Server 2012's SEQUENCE which sounds great, but the problem is that I would still need a seperate sequence per client

i.e. 

CREATE SEQUENCE InvoiceNum_Client1.....
CREATE SEQUENCE InvoiceNum_Client2.....
CREATE SEQUENCE InvoiceNum_Client3.....

but something feels very dirty about making db meta changes from my app everytime a new client is created. Also then my trigger would have to do something like this (which I wouldn't even begin to know how to do)

NEXT VALUE FOR InvoiceNum_Client + @ClientId

etc

So my next thought was to have a "sequence" table, i.e.

ClientID   INSequenceNumber
1           1
2           3
3           3

And in my trigger grab the InSequenceNumber for a given client, use it to make my invoiceNumber, and then update the sequence table, incrementing InSequenceNumber by 1 for the same client. It should have the same effect, but I am just not sure about the inner workings of transactions and scope etc

So my questions are

  1. Is there any big disadvantage to my self rolled sequence method?
  2. Is there another solution that I am perhaps overlooking?

Thanks!

Improve this question
1
  • I appreciate it's almost 10 years ago 😀, but what solution did you go with, and did you regret anything? Commented Jul 4, 2024 at 10:31

2 Answers 2

Reset to default
1

Is there a specific reason for requiring that the invoice numbers are only unique per client? In most ERP systems, invoice numbers are typically globally unique, making implementation much easier. No matter what, you should have an Invoice table that contains a primary key (and you shouldn't use composite primary keys - that's just downright bad data modelling).

This leaves us with a scenario where you might not need to store the "per-client-invoice-number" in the database at all. Assuming you have a table called "Invoices" containing the following data:

Id   | ClientId
---------------
  1  |        1
  2  |        1
  3  |        2
  4  |        1
  5  |        3
  6  |        2

Here, Id is the Primary Key of the Invoices table, and ClientId is a foreign key. A query like this:

SELECT 
    ClientId,
    'IN' + RIGHT('0000' + 
             CONVERT(VARCHAR, ROW_NUMBER() OVER (PARTITION BY ClientId 
                                                 ORDER BY Id)) AS InvoiceNo,
    Id
FROM Invoices
ORDER BY ClientId, InvoiceNo

Would return:

ClientId | InvoiceNo | Id
---------------------------
      1  |    IN0001 |  1
      1  |    IN0002 |  2
      1  |    IN0003 |  4
      2  |    IN0001 |  3
      2  |    IN0002 |  6
      3  |    IN0001 |  5
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

very nice answer thanks. I didnt realise there was the OVER / PARTITION BY key words. very powerful. TO answer your 1st question, there is a valid use case for having unique invoice numbers per client. this is a shared system used by more than one client, and each client is completely seperate from one another. If I use a global unique invoice sequence, the clients will experiance "Holes" in their invoice sequences, and that will get the auditors asking questions
I see. In that case, you should definitely still persist the per-client-invoice numbers into the table. But I'm afraid the SEQUENCE operator won't help you much in this regard. Instead, you should use the table approach that you suggested yourself, or use MAX() together with a locking mechanism to ensure duplicate invoice numbers are never created.
@Dan . . . In SQL Server, never use varchar without a length. You never know if the default length in a given context is going to be sufficient. So, you should have CONVERT(VARCHAR(255), . . . (or whatever your favorite length is).
@GordonLinoff: It's a good practice to always specify a length for sure. In this context, however, the default length is always 30, which is plenty. According to the documentation for char and varchar, you are guaranteed a length of 30 when you don't specify the length in a CONVERT statement.
1

Why do the clients have to have their own sequences? Have a global sequence number. Then, if you want to get the client sequences in order, use row_number():

select i.*, row_number() over (partition by clientid order by invoiceno) as ClientInvoiceSequence
from invoices;

Note: you might want the order by to be by another field such as date.

If you start storing this information in the database, you will need to do a lot of bookkeeping and careful transaction management:

  • What happens when two invoices are entered "at the same time"?
  • What happens when an invoice is deleted?
  • What happens when an invoice is modified in such a way that it might change the sequence?

You are much better off using an identity column and calculating the sequence when you need it.

Improve this answer

1 Comment

Thanks, also a very good answer. very similar to dan but I gav him the points because he beat u to the punch, if only by a few short seconds :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.