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

Question

我正在为特许经营的 CRM 设计关系数据库的第二次主要迭代（进行了大量重构），并且我需要有关存储工作发票和发票行<的最佳数据库设计实践的帮助。 /strong> 对每张发票所做的任何更改都有强大的审计跟踪。

当前架构

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 表中存储“已付款”状态是否有意义？

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

我可以通过在发票修订表中存储状态更改以及发票总额和审核用户来跟踪发票的修订（请参阅上面的InvoiceRevisions ），但跟踪发票行修订表感觉很难维护。想法？ 编辑：订单项应该是不可变的。这适用于“草稿”发票。

3. 税收

在存储发票数据时，我应该如何合并销售税（或南非的 14% 增值税）？

---

编辑：很好的反馈，伙计们。 发票和发票行根据定义是不可变的，因此跟踪更改是不明智的。然而，“草稿”发票在开具之前必须可由多人编辑（例如，经理在技术人员创建发票后应用折扣）...

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

1. 草案
2. 发布
3. 无效

……被迫朝一个方向改变？

Answer 1

在使用别人设计的发票系统后端大约 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,
    ...
)

看看我做了什么在这里吗？我们的发票是原始的、神圣的实体，没有被一些第一天上班的客户服务人员任意更改所玷污。这里没有搞砸的风险。但是，如果需要，我们仍然可以找到发票的整个“历史记录”，因为它链接回其原始订单 - 如果您还记得的话，我们不允许更改离开活动状态后。

这正确地代表了现实世界中正在发生的事情。发票一旦发送/过帐，就无法收回。它就在那里。如果您想取消它，则必须将冲销过帐到应收帐款（如果您的系统支持此类操作）或作为负发票以满足您的财务报告要求。如果这样做了，您实际上可以查看发生了什么，而无需深入研究每张发票的审计历史记录；您只需查看发票本身即可。

仍然存在这样的问题：开发人员必须记住在将订单状态作为发票发布后更改订单状态，但我们可以通过触发器来解决这个问题：

CREATE TRIGGER tr_Invoices_UpdateOrderStatus
ON Invoices
FOR INSERT
AS

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

现在您的数据对于粗心的用户甚至粗心的开发人员来说都是安全的。发票不再含糊不清；您不必担心因为有人忘记检查发票状态而出现错误，因为没有状态。

因此，只是重新总结和解释其中的一些内容：为什么我为了一些发票历史记录而费尽心思？

因为尚未过帐的发票不是真正的交易。它们是事务“状态”——正在进行的事务。它们不属于您的交易数据。通过像这样将它们分开，您将解决很多未来潜在的问题。

免责声明：这都是我的个人经验，我并没有见过世界上的所有发票系统。我不能 100% 保证这适合您的特定应用。我只能重申我所看到的由“待处理”发票概念以及将状态数据与事务数据混合而产生的问题。

与您在互联网上找到的所有其他设计一样，您应该将其作为一种可能的选择进行研究，并评估它是否真正适合您。

Answer 2

通常发票行不会改变。即订单（采购订单或工​​作订单）成为发票。一旦发票开出，它就可以作废，或者可以应用付款和贷项凭证，但通常仅此而已。

您的情况可能有点不同，但我相信这是通常的惯例 - 毕竟，当您收到发票 xyz 时，您不会期望文档所依据的数据会以任何方式更改。

就税收而言，根据我的经验，税收通常存储在发票级别并在过帐发票时确定。

就订单在成为发票之前发生的变化而言，通常我没有见过比基本数据库级审核更复杂的事情 - 通常应用程序不会向用户公开该历史记录。

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

我们通常没有“草稿发票”。这很诱人，因为订单和发票之间有很多相似之处。但实际上，最好将未成为发票的订单放在单独的表中。发票往往有一些差异（即状态更改实际上是从一个实体到另一个实体的转换），并且具有引用完整性，有时您实际上只希望事物加入到“真实”发票中。

所以我们通常总是有PurchaseOrder、PurchaseOrderLine、Invoice和InvoiceLine。在某些情况下，我让 PO 端的行为更像购物车 - 价格不存储，而是随产品表浮动，而在其他情况下，它们更像是报价，一旦传输到订单，就必须遵守报价。客户。在查看业务工作流程和需求时，这些微妙之处可能很重要。

Answer 3

为什么不直接创建要审核的表的副本，而不是在原始表上创建触发器，在每次插入、更新、删除时将行复制到表副本？

触发器通常看起来像这样：

CREATE TRIGGER Trg_MyTrigger
   ON  MyTable
   AFTER UPDATE,DELETE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [DB].[dbo].[MyTable_Audit]
           (Field1, Field2)
     SELECT Field1, Field2
    FROM DELETED
END
GO

Answer 4

我同意 Aaronaught 上面关于发票“不变性”的评论。

如果您采纳该建议，那么我会考虑将“待审核”、“已批准”和“已作废”作为状态。 “待审核”就是这样。 “已批准”被视为正确，并由客户付款。 “作废”是指：发票不再有效，客户不再支付。然后，您可以从 Payments 中的记录推断发票是否已全额支付，并且您不会重复信息。

除此之外，你的修改想法没有真正的问题。

您可以将税费作为 InvoiceLines 中的另一条记录包含在内。