发票,发票行和修订的数据库设计

我正在设计特许经营CRM(具有大量重构)的关系数据库的第二次主要迭代,我需要有关存储工作发票和发票行的最佳数据库设计实践的帮助,并对每个更改进行强有力的审计跟踪发票.

当前架构

Invoices 表

InvoiceId (int) // Primary key
JobId (int)
StatusId (tinyint) // Pending, Paid or Deleted
UserId (int) // auditing user
Reference (nvarchar(256)) // unique natural string key with invoice number
Date (datetime)
Comments (nvarchar(MAX))

InvoiceLines 表

LineId (int) // Primary key
InvoiceId (int) // related to Invoices above
Quantity (decimal(9,4))
Title (nvarchar(512))
Comment (nvarchar(512))
UnitPrice (smallmoney)

修订方案

InvoiceRevisions 表

RevisionId (int) // Primary key
InvoiceId (int)
JobId (int)
StatusId (tinyint) // Pending, Paid or Deleted
UserId (int) // auditing user
Reference (nvarchar(256)) // unique natural string key with invoice number
Date (datetime)
Total (smallmoney)

架构设计考虑因素

1.存储发票的付费或待定状态是否合理？

收到的所有发票付款都存储在一张Payments表格中(例如现金,信用卡,支票,银行存款).Invoices如果可以从Payments表中推断出与给定作业的发票相关的所有收入,那么在表中存储"付费"状态是否有意义？

2.如何跟踪发票行项目修订？

我可以跟踪修订的发票通过存储状态的变化与发票总额,并在审核用户一起发票修订表(见InvoiceRevisions上文),但保留发票行修订表的赛道感觉很难维持.思考？编辑:订单项应该是不可变的.这适用于"草稿"发票.

3.税

存储发票数据时,如何纳入销售税(或SA中的14%增值税)？

---

编辑:很好的反馈,伙计们.根据定义,发票和发票行是不可变的,因此跟踪变更是不明智的.但是,"草稿"发票必须可由多个人编辑(例如,经理在技术人员创建发票后应用折扣)才能发出...

4.定义和跟踪发票状态的最佳方式？

草案

发行

作废

......限制在一个方向上改变？

1> Aaronaught..：

---

我的建议是大约4年不得不使用其他人设计的发票系统的后端:发票上没有"待定"状态.它会让你疯狂.

将待处理发票存储为普通发票(具有"待定"标志/状态)的问题在于,将有数百个操作/报告仅考虑已过帐的发票,这实际上意味着除待处理之外的每个状态.这意味着必须每次检查此状态.单.时间. 有人会忘记.在任何人意识到它之前几周.

您可以ActiveInvoices使用内置的挂起过滤器创建视图,但这只会改变问题; 有人会忘记使用视图而不是表格.

待处理发票不是发票.它在问题评论中正确地说明为草案(或命令,请求等,所有相同的概念).明确地说,能够修改这些草稿的必要性是可以理解的.所以这是我的建议.

首先,创建一个草稿表(我们称之为Orders):

CREATE TABLE Orders
(
    OrderID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED,
    OrderDate datetime NOT NULL
        CONSTRAINT DF_Orders_OrderDate DEFAULT GETDATE(),
    OrderStatus tinyint NOT NULL,  -- 0 = Active, 1 = Canceled, 2 = Invoiced
    ...
)

CREATE TABLE OrderDetails
(
    -- Optional, if individual details need to be referenced
    OrderDetailID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_OrderDetails PRIMARY KEY CLUSTERED,
    OrderID int NOT NULL
        CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY
            REFERENCES Orders (OrderID)
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    ...
)

CREATE INDEX IX_OrderDetails
ON OrderDetails (OrderID)
INCLUDE (...)

这些是您的基本"草稿"表格.他们可以改变.要跟踪更改,您应创建历史记录表,其中包含原始Orders和OrderDetails表中的所有列,以及上次修改的用户,日期和修改类型(插入,更新或删除)的审计列.

正如Cade所提到的,您可以使用AutoAudit自动执行此过程的大部分过程.

您还需要的是阻止更新不再有效的草稿的触发器(尤其是已过帐且已成为发票的草稿).保持这些数据的一致性非常重要:

CREATE TRIGGER tr_Orders_ActiveUpdatesOnly
ON Orders
FOR UPDATE, DELETE
AS

IF EXISTS
(
    SELECT 1
    FROM deleted
    WHERE OrderStatus <> 0
)
BEGIN
    RAISERROR('Cannot modify a posted/canceled order.', 16, 1)
    ROLLBACK
END

由于发票是两级层次结构,因此您需要一个类似且稍微复杂的触发器来获取详细信息:

CREATE TRIGGER tr_OrderDetails_ActiveUpdatesOnly
ON OrderDetails
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS
(
    SELECT 1
    FROM
    (
        SELECT OrderID FROM deleted
        UNION ALL
        SELECT OrderID FROM inserted
    ) d
    INNER JOIN Orders o
        ON o.OrderID = d.OrderID
    WHERE o.OrderStatus <> 0
)
BEGIN
    RAISERROR('Cannot change details for a posted/canceled order.', 16, 1)
    ROLLBACK
END

这可能看起来很多工作,但现在你可以这样做:

CREATE TABLE Invoices
(
    InvoiceID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Invoices PRIMARY KEY CLUSTERED,
    OrderID int NOT NULL
        CONSTRAINT FK_Invoices_Orders FOREIGN KEY
            REFERENCES Orders (OrderID),
    InvoiceDate datetime NOT NULL
        CONSTRAINT DF_Invoices_Date DEFAULT GETDATE(),
    IsPaid bit NOT NULL
        CONSTRAINT DF_Invoices_IsPaid DEFAULT 0,
    ...
)

看看我在这做了什么？我们的发票是原始的,神圣的实体,没有被一些第一天的客户服务员随意改变而玷污.这里没有搞砸的风险.但是,如果我们需要,我们仍然可以找到发票的整个"历史记录",因为它会链接回原始发票Order- 如果您记得,我们不会允许在发票处于活动状态后进行更改.

这正确地代表了现实世界中正在发生的事情.发送/过帐发票后,将无法收回.它就在那里.如果您要取消它,您必须向A/R(如果您的系统支持此类事情)或作为负面发票发布撤消以满足您的财务报告.如果这样做,您实际上可以看到发生的事情,而无需深入了解每张发票的审计历史记录; 你只需要查看发票本身.

仍有一个问题,开发人员必须记住在将其作为发票过帐后更改订单状态,但我们可以通过触发器来解决这个问题:

CREATE TRIGGER tr_Invoices_UpdateOrderStatus
ON Invoices
FOR INSERT
AS

UPDATE Orders
SET OrderStatus = 2
WHERE OrderID IN (SELECT OrderID FROM inserted)

现在,您的数据对于粗心的用户甚至粗心的开发人员都是安全的.发票不再含糊不清; 你不必担心漏洞,因为有人忘了检查发票状态,因为没有状态.

所以只是重新总结并解释其中的一些问题:为什么我只是因为某些发票历史而遇到了所有麻烦？

因为尚未发布的发票不是真实的交易.它们是交易"状态" - 正在进行的交易.它们不属于您的交易数据.通过像这样将它们分开,您将解决许多潜在的未来问题.

免责声明:这完全取决于我个人的经验,我没有看到世界上的每个发票系统.我无法100%确定这是否适合您的特定应用.我只能重申我从"待处理"发票的概念中看到的大黄蜂问题,从状态数据与交易数据混合.

与您在互联网上找到的所有其他设计一样,您应该将此作为一种可能的选项进行调查,并评估它是否真的适合您.

---

评论后:我不想进入*令人难以忍受的*详细信息,但如果您正在跟踪付款,则不需要"IsPaid"列.最好是针对个别发票应用付款,如果单个付款涵盖多个发票,则将其拆分为存储桶,并维护从付款明细到发票的引用,并包括溢出存储桶.也就是说,您可能仍然希望将"IsPaid"或"PaidDate"列作为非规范化的一种形式,因为未付发票是老化过程的常见输入.

2> Cade Roux..：

---

通常,发票行不会更改.即订单(采购订单或工​​单)成为发票.发票开具后,可以取消发票,也可以应用付款和贷记凭证,但这通常是关于它的.

您的情况可能会有所不同,但我认为这是通常的约定 - 毕竟,当您收到xyz发票时,您不希望文档所基于的数据以任何方式被更改.

至于税收(通常根据我的经验),存储在发票级别并在发票过帐时确定.

至于订单在成为发票之前发生变化,通常我看到的不比基本的数据库级审计复杂 - 通常应用程序不会将该历史记录暴露给用户.

如果您想要一个相对领域无关的直接审计跟踪,您可以查看AutoAudit - 基于触发器的审计跟踪.

我们通常没有"草稿发票".这很诱人,因为订单和发票之间有很多相似之处.但实际上,最好是在单独的表格中没有成为发票的订单.发票往往会有一些差异(即状态变化实际上是从一个实体到另一个实体的转换),并且有时参考完整性,您实际上只希望加入"真实"发票.

所以我们通常总是有PurchaseOrder,PurchaseOrderLine,Invoice和InvoiceLine.在某些情况下,我认为PO方面的行为更像是购物车 - 价格没有存储,并且浮动产品表和其他情况,它们更像是价格报价,一旦传送到客户.在查看业务工作流程和要求时,这些细微之处可能很重要.