需要为多租户应用中的发票数据库设计提供一些建议

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的表，它包含生成发票所需的所有信息。发票表将包含发票编号，发票创建日期和增值税率。我想为每个租户创建一个Sequence对象来生成发票号。

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).

在数据库中有数百个Sequence对象是否可以，因为我必须为每个租户创建一个？我还必须创建相同数量的存储过程，它们返回下一个发票号（我更喜欢每个租户的单独存储过程，而不是在select case语句中有一个大型存储过程和数百个选项）。

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?

另一个问题是，理论上是基于事务表（EmployeePay）插入主表（Invoice）然后使用其主键（InvoiceID）来更新事务表吗？

Thanks in advance.

提前致谢。

2 个解决方案

#1

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. 对于MySQL检查此链接。
3. If you are using Oracle then check this LINK
4. 如果您使用的是Oracle，请检查此LINK

#2

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

我建议你创建另一个表可以调用InvoiceNumber，这将包含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).

CurrentSequenceNumber的意义在于它将是一个简单的整数，可用于生成下一个Invoicenumber.InvoiceNumberId将是主键用途的标识列（您可能拥有也可能没有）。

Structure of the Table will look like below.

表的结构如下所示。

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.

现在您只需创建一个存储过程，它将输入参数作为TenantId，并且有责任通过从上表中读取CurrentSequenceNumber来生成下一个发票编号。

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.

例如，如果我们需要为id为15的租户生成新的Invoice Id，那么SP将拥有您的业务逻辑我假设只是创建一个字符串，其中“Inv-”作为前缀，并增加CurrentSequenceNumber的值，因此Procedure的输出将是。

Inv-0009

INV-0009

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

然后，在生成此数字后，SP将为InvoiceNumberId 3将值增加到9。

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

所以一切都将由Single table和Single过程管理。