November 17, 2015 at 4:02 am
Hi all,
I need some guidance on designing the schema for invoices in a multi-tenant application.I have a table called EmployeePay which holds all the information required to generate an invoice. The invoice table would have the invoice number, invoice created date, VAT rate and TenantID.
I am thinking to create a Sequence object for each Tenant to generate an invoice number, because each tenant should have their own set of invoice numbers. Another approach is to have a column (for example LastInvoiceNumber) in the tenant table and increment it by 1, but that may cause a performance bottle neck considering a tenant can have multiple users, so I am not considering that at the moment.
Is it okay to have hundreds of Sequence objects in a database, as I’ll have to create one for each tenant? I’ll also have to create same amount of stored procedures which returns the next invoice number (I prefer a separate stored procedure for each tenant rather than having one large stored procedure with hundreds of choices in a select case statement).
Another concern is, is it theoretical to update the master table (Invoice) based on the transaction table (EmployeePay) and then use its primary key(InvoiceID) to update the transaction table?
EmployeePay Table: EmployeeID, Hours, Rate, InvoiceID
Invoice Table: InvoiceID, InvoiceNumber, InvoiceDate, VATRate, TenantID
November 17, 2015 at 4:46 am
divyang_dv 42143 (11/17/2015)
I am thinking to create a Sequence object for each Tenant to generate an invoice number, because each tenant should have their own set of invoice numbers.
This way you'll rather share the same invoice number among different tenants. What's wrong with unique invoice number within the whole table scope?
November 17, 2015 at 4:54 am
serg-52 (11/17/2015)
divyang_dv 42143 (11/17/2015)
I am thinking to create a Sequence object for each Tenant to generate an invoice number, because each tenant should have their own set of invoice numbers.This way you'll rather share the same invoice number among different tenants. What's wrong with unique invoice number within the whole table scope?
I meant to say one sequence object for one tenant, not one sequence object for all tenants. Sorry if I am not being clear.
November 17, 2015 at 5:24 am
No, you stated it absolutly clear, separate sequence for each tenat. And this will generally allow for the same invoice number =123 to emerge for different tenats.
That's my question, why not to declare INVOCE_NUMBER IDENTITY(..) so that it would be unique?
November 17, 2015 at 5:41 am
Alright, it makes sense, I really haven't thought that I am actually sharing invoice numbers between tenants. Can you please explain in a bit more on how to approach? The main considerations are; performance and each tenant should have their own series.
November 18, 2015 at 1:46 am
divyang_dv 42143 (11/17/2015)
Alright, it makes sense, I really haven't thought that I am actually sharing invoice numbers between tenants. Can you please explain in a bit more on how to approach? The main considerations are; performance and each tenant should have their own series.
Sure one can write some bad code with any data type, but I'm not aware of IDENTITY having any perfomance problems itself.
"each tenant should have their own series." needs clarification. What is "series" in the context of the system under consideration? What is an example of data which breaks this requirement?
November 19, 2015 at 2:02 am
If I have a tenant1 with 10 invoices and then if I add another tenant2, the invoice numbers for tenant2 should start from 1 and not 11.
November 19, 2015 at 2:47 am
divyang_dv 42143 (11/19/2015)
If I have a tenant1 with 10 invoices and then if I add another tenant2, the invoice numbers for tenant2 should start from 1 and not 11.
I see. May i ask why? From my expirience such a requirement generally comes from legacy system, may be even from times before any computerized system was deployed. Or when legacy system was fragmented so that every tenant has its own separate database.
This requirement places much extra burden on integrated system but is it really business justified? What is wrong with system-wide unique invoice numbering?
Nevertheless if it's totally unavoidable proceed with sequences. But separate procedure for every tenant is overcomplicated, just use dynamic sql to access sequence according to tenantId parameter.
November 19, 2015 at 2:56 am
I can see where the OP is coming from.
A solution like FreshBooks would be an ideal example of what the OP wants.
Multiple clients sign up to the companies invoicing system, each client wants to start at invoice 1, not at the next identity value.
My take would be to add some logic to the invoice table
Pseudo code
IF Tenant doesnt exist
Insert invoice 1
IF Tenant does exist, select max(invoiceid)+1 as next invoice
Or have an tenant/invoice parameter table and have some proc like get next invoice number
Tenant signs up, insert into Tenant_Invoice_Param tenantID, 1
Select @invid = max(invoiceid) from tenant_invoice_param where tenantid = @tenant
insert invoice
update tenant_invoice_param set invoiceid = @invid+1 where tenantid = @tenant
Could potentially be better than creating infinite sequences for each new Tenant
November 19, 2015 at 4:10 am
Thanks all for the responses. I think I'll have to drop the idea to use Sequences and would rather use a small table for TenantID, LastInvoiceNumber and increment it.
November 19, 2015 at 4:30 am
This definetly will work. But keep in mind sequences pose almost no locks (and no locks on user tables at all) as they are generated outside the scope of the transaction. It can be an advantage worth a couple of dynamic sql proc.
November 19, 2015 at 4:38 am
The problem I find with the sequence in this case is there will be a hell of a lot of sequences and the need for dynamic sql to build up the syntax to get the next value from the particular clients sequence, unless there is one sproc for insert invoice for each client, but that might get messy if theres a business logic change, would rather change 1 sproc then X tenant sprocs.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply