1

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 and VAT rate. I am thinking to create a Sequence object for each Tenant to generate an invoice number.

EmployeePay Table: EmployeeID, Hours, Rate, InvoiceID (FK)

Invoice Table: InvoiceID (PK) (Identity), InvoiceNumber, InvoiceDate, VATRate, TenantID

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 insert into the master table (Invoice) based on the transaction table (EmployeePay) and then use its primary key(InvoiceID) to update the transaction table?

Thanks in advance.

slava
  • 1,901
  • 6
  • 28
  • 32
Divyang Vyas
  • 65
  • 1
  • 8
  • So basically you want a InvoiceNumber series on Tenant level..means If we generate second Invoice for same tenant then It should be incremented by 1 ?? – Sagar Shirke Nov 17 '15 at 09:02
  • That's correct each tenant should have it's own series of invoice numbers. – Divyang Vyas Nov 17 '15 at 09:03
  • 1
    I would suggest against that. The number of database objects should not be dependent on the number of database users. There are plenty of ways to calculate the next invoice number without having to create a sequence object for each tenant. – Zohar Peled Nov 17 '15 at 09:16
  • @ZoharPeled I agree with you but apart from using a Sequence or having a column and increment it, I really can't think of any better solution. It'll be great if you can share some more methods. – Divyang Vyas Nov 17 '15 at 10:07

2 Answers2

0

First make sure the relationship either this is one to many or many to many. If you are considering one employee that will have many invoices then its one to many relationship and you can create your table as under:

EmployeePay Table: EmployeeID (PK) (Identity), Hours, Rate

Invoice Table: InvoiceID (PK) (Identity), EmployeeID (FK), InvoiceNumber, InvoiceDate, VATRate, TenantID

EDIT:

I don't know which database you are using but for increment sequence check:

  1. for MySQL check this LINK.
  2. If you are using Oracle then check this LINK
Community
  • 1
  • 1
Ghayel
  • 1,113
  • 2
  • 10
  • 19
  • I am using SQL Server and its a one to many relationship, one Invoice can have many EmployeePay details. – Divyang Vyas Nov 17 '15 at 09:34
  • If one invoice and may employeePay then your created schema is ok. apply it and if there is one employee and many invoices then follow my directives. Please don't forget to mark my answer if you like it – Ghayel Nov 17 '15 at 09:44
0

I would suggest you to create another table can be called as InvoiceNumber, this will contain InvoiceNumberId(Int),TenantId (Fk), CurrentSequenceNumber(Int).

Significance of CurrentSequenceNumber is that it will be simple integer number which can be used to generate next Invoicenumber.InvoiceNumberId will be a Identity columns for Primary key purpose (you may or may not have it).

Structure of the Table will look like below.

enter image description here

Now you need to create only One Stored Procedure which will take input parameter as TenantId and will have responsiblity to generate next Invoice number by reading CurrentSequenceNumber from above table.

For example if we need to generate new Invoice Id for Tenant with id as 15 then SP will have your Business logic I am assuming Just creating a String with "Inv-" as prefix with incremented value of CurrentSequenceNumber so output of Procedure will be.

Inv-0009

Then after generation of this number SP will increment value to 9 for InvoiceNumberId 3.

So everything will be managed by Single table and Single procedure only.

Sagar Shirke
  • 648
  • 9
  • 32
  • In your sequence table, why have the [InvoiceNumberId] as PK? Each tenant will only have have one sequence, so use that. And the sequence is not an 'Id', it's just a number. – Tony Nov 17 '15 at 09:20
  • @Tony..you are right SequenceId is not a proper coulmn name it is just a number I will update it... Also there were atleast two ways to have primary key. 1 was use tenantId-SquenceId as composite promary Key or second way have another Identity column as primary key, I took second option. – Sagar Shirke Nov 17 '15 at 09:23
  • No need for a composite key either, [TenantId] on it's own is sufficient as the PK. – Tony Nov 17 '15 at 09:31
  • I've though about this approach earlier but it may cause a performance bottleneck (http://stackoverflow.com/questions/1104741/generating-sequential-numbers-in-multi-user-saas-application). So I've decided to use the Sequence Object instead which can be implemented very neatly. – Divyang Vyas Nov 17 '15 at 09:38
  • @Tony...Yes I agree with you..that will be more correct design. – Sagar Shirke Nov 17 '15 at 09:38
  • @DivyangVyas..The link which you have shared indicates there can be performance problem if single row will be updated by multiple entities. But in your case I am not sure whether one particular CurrentSequenceNumber will be used simultaneously by more than one entity. As per my knowledge reading your description it is highly unlikely. – Sagar Shirke Nov 17 '15 at 09:44
  • @SagarShirke One tenant will have multiple users,I should have mentioned this earlier, so sorry for that. – Divyang Vyas Nov 17 '15 at 09:51
  • @DivyangVyas..As per your business knowledge about this situation..what is the possibility that same entry will be used for different users at a time..means will Tenant will have many users associated with it? – Sagar Shirke Nov 17 '15 at 09:56
  • It varies but yes we have tenants with 70-100 users and I also have to consider future proofing. – Divyang Vyas Nov 17 '15 at 10:00
  • @Tony Do we even need a different table? Why can't I put the CurrentSequenceNumber in the Tenant's table itself as it would be a one-to-One relationship. – Divyang Vyas Nov 17 '15 at 13:45
  • @DivyangVyas - I was mainly commenting on the redundant PK in the table, but yes, the information could be added to the main tenant table. But if you are worried about performance (specifically write contention) it would be better to have a separate, smaller, table. – Tony Nov 17 '15 at 14:01