发票，发票行和数据库的数据库设计修订 [英] Database design for invoices, invoice lines & revisions

问题描述

我正在为特许经营的CRM（有大量重构）的关系数据库进行第二次重大迭代，而且我需要帮助存储工作发票和的最佳数据库设计实践

I'm designing the 2nd major iteration of a relational database for a franchise's CRM (with lots of refactoring) and I need help on the best database design practices for storing job invoices and invoice lines with a strong audit trail of any changes made to each invoice.

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 表

InvoiceLines Table

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

修订架构

InvoiceRevisions 表

Revision schema

InvoiceRevisions Table

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。存储发票的付费或待处理状态是否合理？

发票上收到的所有付款都存储在付款中表（例如现金，信用卡，支票，银行存款）。如果所有与指定工作发票相关的所有收入都可以从付款中推断出来，那么在发票表中存储已付状态是有意义的， / code>表？

Schema design considerations

1. Is it sensible to store an invoice's Paid or Pending status?

All payments received for an invoice are stored in a Payments table (eg. Cash, Credit Card, Cheque, Bank Deposit). Is it meaningful to store a "Paid" status in the Invoices table if all the income related to a given job's invoices can be inferred from the Payments table?

我可以通过存储状态更改以及发票总额跟踪追踪发票的修订版本在发票修订表中审核用户（见上述 InvoiceRevisions ），但跟踪发票行修订表感到难以维护。想法？ 编辑：订单项应该是不可变的。这适用于起草发票。

I can track revisions to an invoice by storing status changes along with the invoice total and the auditing user in an invoice revision table (see InvoiceRevisions above), but keeping track of an invoice line revision table feels hard to maintain. Thoughts? line items should be immutable. This applies to a "draft" invoice.

存储发票数据时，应如何纳入销售税（SA中的14％增值税）？

How should I incorporate sales tax (or 14% VAT in SA) when storing invoice data?

编辑：好的反馈，伙计们。 发票和发票行根据定义不可变，因此跟踪更改不明确。但是，起草发票必须由多个人编辑（例如，经理员在技术人员创建发票后应用折扣）...

Good feedback, guys. Invoices and invoice lines are by definition immutable, so tracking changes isn't sensible. However, a "draft" invoice must be editable by more than one person (eg. manager applies discount after technician creates invoice) before it is issued...

1. 草稿


2. 发行


3. Voided

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

...constrained to change in one direction?

推荐答案

我的建议是从大约4年的时间开始使用发票系统的后端设计某人设计的：没有发票上的待定状态。这将导致您疯狂。

My advice from about 4 years of having to work with the back-end of an invoicing system that somebody else designed: Don't have a "pending" status on invoices. It will drive you insane.

将待发票作为普通发票（具有待定标志/状态）存储的问题是，将有数百个操作/报告这仅仅是考虑到发布的发票，这意味着除了之外的每个状态都是待处理的。这意味着每个都必须检查这个状态。单。时间。有人会忘记。

The problem with storing pending invoices as ordinary invoices (with a "pending" flag/status) is that there will be hundreds of operations/reports that are only supposed to take into account posted invoices, which literally means every status except for pending. Which means that this status has to be checked every. single. time. And somebody is going to forget. And it will be weeks before anybody realizes it.

您可以使用内置的待处理过滤器创建一个 ActiveInvoices 视图但这只是转移问题;有人会忘记使用视图而不是表格。

You can create an ActiveInvoices view with the pending filter built in, but that just shifts the problem; somebody will forget to use the view instead of the table.

待处理的发票不是发票。在问题评论中正确地声明为草案（或订单，请求等等，所有相同的概念）。必须能够修改这些草稿是可以理解的。所以这是我的建议。

A pending invoice is not an invoice. It is correctly stated in the question comments as a draft (or an order, request, etc., all the same concept). The need to be able to modify these drafts is understandable, definitely. So here's my recommendation.

首先，创建一个草图表（我们称之为 Orders ）：

First, create a draft table (we'll call it 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 表，以及上次修改用户，日期和修改类型（插入，更新或删除）的审核列。

These are your basic "draft" tables. They can be changed. To track the changes, you should create history tables, which have all of the columns that are in the original Orders and OrderDetails tables, plus audit columns for the last modified user, date, and modification type (insert, update, or delete).

正如Cade所提及的，您可以使用 AutoAudit 可以自动执行此过程的大部分。

As Cade mentions, you can use AutoAudit to automate most of this process.

我们也希望是一个触发器来防止更新不再活跃的草稿（特​​别是已发布并已成为发票的草稿）。保持这个数据是一致的：

What you'll also want is a trigger to prevent updates to drafts that are no longer active (especially drafts that are posted and have become invoices). It's important to keep this data consistent:

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

由于发票是一个两层次的层次结构，因此您需要一个类似且稍微复杂的触发器：

Since invoices are a two-level hierarchy, you need a similar and slightly more complicated trigger for the details:

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

这可能看起来像很多工作，但现在你可以这样做：

This may seem like a lot of work, but now you get to do this:

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

看到我在这里做了什么？我们的发票是原始的，神圣的实体，由一些第一天在职客户服务人员的任意改变而不舒服。在这里没有风险。但是，如果我们需要，我们仍然可以找到发票的整个历史，因为它链接到原来的订单 - 如果你记得，我们不会在离开活动状态后进行更改。

See what I did here? Our invoices are pristine, sacred entities, un-sullied by arbitrary changes by some first-day-on-the-job customer service guy. There is no risk of screwing up here. But, if we need to, we can still find out the entire "history" of an invoice because it links back to its original Order - which, if you'll recall, we are not allowing changes to after it leaves the active status.

这正确表示现实世界中发生了什么。一旦发送/发布发票，就不能收回。在那里如果你想取消它，你必须向A / R（如果您的系统支持那种事情）或作为负发票发布一个逆转，以满足您的财务报告。如果这样做，您可以实际查看发生的情况，而无需考虑每个发票的审计历史;您只需要查看发票本身。

This correctly represents what's going on in the real world. Once an invoice is sent/posted, it can't be taken back. It's out there. If you want to cancel it, you have to post a reversal, either to an A/R (if your system supports that sort of thing) or as a negative invoice to satisfy your financial reporting. And if this is done, you can actually see what happened without having to dig into the audit history for each invoice; you just have to look at the invoices themselves.

仍然有一些问题，开发人员必须记住在将其作为发票发布后更改订单状态，但我们可以用触发器补救：

There's still the problem that developers have to remember to change the order status after it's been posted as an invoice, but we can remedy that with a trigger:

CREATE TRIGGER tr_Invoices_UpdateOrderStatus
ON Invoices
FOR INSERT
AS

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

现在，您的数据对于粗心大意的用户甚至不小心的开发人员来说都是安全的。发票不再模糊;你不必担心因为有人忘记检查发票状态，因为没有状态。

Now your data is safe from careless users and even careless developers. And invoices are no longer ambiguous; you don't have to be worry about bugs creeping in because somebody forgot to check the invoice status, because there is no status.

只是为了重新总结和解释一些：为什么我只是为了一些发票历史而去了所有这些麻烦？

So just to re-summarize and paraphrase some of this: Why have I gone to all this trouble just for some invoice history?

因为尚未发布的发票不是真正的交易。它们是交易状态 - 正在进行的交易。它们不属于您的交易数据。通过这样保持分开，您将解决很多潜在的未来问题。

Because invoices that haven't been posted yet aren't real transactions. They are transaction "state" - transactions in progress. They don't belong with your transactional data. By keeping them separate like this, you will solve a lot of potential future problems.

免责声明：这是所有这些都来自于我的个人经历，我没有看到世界上每个发票系统。我不能保证100％确定这适合您的特定应用。我只能重申我从待定发票的概念中所看到的大黄蜂巢的问题，将国家数据与交易数据混合起来。

Disclaimer: This is all speaking from my personal experience and I have not seen every invoicing system in the world. I can't guarantee with 100% certainty that this is suitable for your particular application. I can only reiterate the hornet's nest of problems I've seen resulting from the notion of "pending" invoices, from mixing state data with transactional data.

您在互联网上找到的设计，您应该将其作为一个可能的选项进行调查，并评估是否可以真正为您服务。

As with every other design you find on the internet, you should investigate this as one possible option and evaluate whether or not it can really work for you.

这篇关于发票，发票行和数据库的数据库设计修订的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！