发票和付款的架构设计-比M：M关系更好的模型？ [英] Schema Design for Invoices and Payments - better model than M:M relationship?

问题描述

在我现有的架构中，发票和项目之间存在1：1的关系。我正在对其进行修改，以允许一个项目具有许多发票，但是我还需要跟踪付款。

There is a 1:1 relationship between invoices and projects in my existing schema. I am in the process of modifying it to allow for one project to have many invoices, but I also need to track payments.

当前，单笔付款涉及1（或更多）的发票（实际上是发票批）。理想情况下，可以跟踪每张发票的多次付款。为此，我认为发票和付款之间必须存在M：M关系。

Currently, a single payment relates to 1 (or more) invoices (really it's an Invoice Batch). Ideally, multiple payments per invoice can be tracked. To do that, I think there needs to be a M:M relationship between invoices and payments.

因此，一旦存储了数据，如何确定数据量还剩下一个项目？如何确定单个客户的欠款？我想不出如何正确分组和求和。

So, once the data is stored, how does one determine how much is left to pay on a single Project? How does one determine how much a single customer owes? I can't think of how to properly group and sum.

到目前为止，我已经提出了几种方法，方法1-3在不同程度上根据以下代码中的表/值。我刚才想出了方法4，尽管它不能让您确定在所有情况下单个项目的欠款，但目前似乎是一个可行的选择。

I have came up short with a couple methods so far, methods 1-3 are, to varying degrees, based on the tables/values in the code below. I came up with method 4 just now, and currently seems like a workable option, though it doesn't allow you to determine what's owed on a single project in all scenarios.

1. 创建3个依赖关系类型的查询（#payinv表中的relType字段），其中2个我知道如何进行。例如，在 relType =‘1：M’的情况下，我可以在1边分组，求和。反之亦然， relType =‘M：1’。但是当 relType ='M：M 时，我很茫然。

1. Create 3 different queries that rely on the relationship type (relType field in the #payinv table), 2 of which I know how to make. E.g., where relType = '1:M', I can group on the 1 side, and sum the many side; and vice versa for relType = 'M:1'. But when relType = 'M:M I am at a loss.

包括属性<每个发票/付款上的code> ProjectID 和 BillToPartyID 。我可以汇总并比较单个项目的所有发票和付款，但是当付款涵盖多个发票时，似乎为该项目支付了太多费用。如果我掏出BillToParty欠的钱，就无法查明他们仍欠哪个项目。

By including the attributes ProjectID and BillToPartyID on each Invoice/Payment. I can sum and compare all the invoices and payments for a single project, but when a payment covers multiple invoices, it will appear that too much has been paid for that one project. If I pull what the BillToParty owes, I can't pinpoint which project they still owe money on.

我可以尝试将发票和付款值存储在连接表。例如，当输入付款时，用户会将付款应用于发票。系统将分三步确定要在用户试图创建的记录中输入哪些值。 ＃1它将通过将联结表中已经存在的当前发票金额相加，然后从原始发票金额中减去该值来计算剩余余额。 ＃2它将以类似方式计算可用于支付的剩余资金。 ＃3它将余额与可用资金进行比较，然后将付款和发票金额中的较小者输入到联结表中。我看到三种3种可能性：
a）如果发票的余额为0，则付款和发票都将关闭以备将来使用。
b）如果发票的余额少于付款，则发票将关闭，并且付款仍可应用于其他发票。
c）如果发票的余额大于付款额，则关闭付款，发票保持未结。除了复杂性，我没有这个想法的问题。

I can try to store invoice and payment values in the junction table. E.g., when entering payments, the user would apply a payment to an invoice. The system would determine what values to enter in the record the user is trying to create in 3 steps. #1 It would calculate the remaining balance by summing the amounts of the current invoice already in the junction table, then subtracting that value from the original invoice amount. #2 It would calculate the remaining funds available for the payment in a similar way. #3 it would compare the remaining balance with the funds available and enter the lesser of both amounts for both payment and invoice into the junction table. I see three 3 possibilities: a) If the remaining balance of the invoice is 0, both the payment and invoice are closed for further use. b) If the remaining balance of the invoice is less than the payment, the invoice is closed and the payment remains available to be applied to a different invoice. c) If the remaining balance of the invoice is greater than the payment, the payment is closed and the invoice remains open. Other than complexity, I don't see problems with this idea...

我可以将BillToParty分配给一个发票批次，发票批次将是发票和付款的父项。如果客户付款不足，我们可以计算剩余的余额并生成新的发票（按原始意义，这不是发票，并且可能不想存储？）。如果他们多付了钱，我们就必须贷记，因为我们不能将付款应用到另一批。

I can assign the BillToParty to an Invoice Batch, the Invoice Batch will be the parent of both Invoices and Payments. If a customer underpays, we can calculate the remaining balance and generate a new "invoice" (Not an invoice in the original sense, and probably don't want to store this???). If they overpay, we must issue a credit because we cannot apply the payment to another batch.

    CREATE TABLE #inv
        (
        invID int NOT NULL,
        invAMT int NULL
        )  ON [PRIMARY]
    GO
    ALTER TABLE #inv ADD CONSTRAINT
        PK_inv PRIMARY KEY CLUSTERED 
        (
        invID
        )
    CREATE TABLE #pay
        (
        payID varchar(50) NOT NULL,
        payAMT int NULL
        )  ON [PRIMARY]
    GO
    ALTER TABLE #pay ADD CONSTRAINT
        PK_pay PRIMARY KEY CLUSTERED 
        (
        payID
        ) 
    CREATE TABLE #payinv
        (
        payID varchar(50) NOT NULL,
        invID int NOT NULL,
        relType varchar(50) not null
        )  ON [PRIMARY]
    GO
    ALTER TABLE #payinv ADD CONSTRAINT
        PK_payinv PRIMARY KEY CLUSTERED 
        (
        payID,
        invID
        )
    INSERT INTO #inv (invID, invAMT)
    select 1,110
    union
    select 2,400
    union
    select 3,600
    union
    select 4,100000
    union
    select 5,10000
    union
    select 6,1000000;

    INSERT INTO #pay (payID,payAMT)
    select 'a',10
    union
    select 'b',100
    union
    select 'c',1000
    union
    select 'd',10000
    union
    select 'e',100000
    union
    select 'f',1000000;

    INSERT INTO #payinv(payID,invID,relType)
    select 'a',1,'1:M'
    union
    select 'b',1,'1:M'
    union
    select 'c',2,'M:1'
    union
    select 'c',3,'M:1'
    union
    select 'd',4,'M:M'
    union
    select 'e',4,'M:M'
    union
    select 'e',5,'M:M'
    union
    select 'f',6,'1:1';

    select #inv.invAMT, #inv.invID, #pay.payID, #pay.payAMT, #payinv.relType
    from #inv inner join #payinv on #inv.invID = #payinv.invID inner join #pay on #payinv.payID = #pay.payID

推荐答案

您需要在以下位置输入billToPartyID（或customerID，我认为它们是同一对象，或者它们之间存在1-> 1关系）您的项目，发票和付款表作为PK的一部分。然后，您的关系为：

You need your billToPartyID (or customerID, I'm assuming they're the same thing, or there's a 1->1 relationship between them) in your project, invoice, and payment tables as part of the PK. Your relationships are then:

一个客户/ billToParty可以有很多项目，可以有很多发票。客户/ billToParty的付款可以应用于许多客户的发票，必须指定。

"a customer/billToParty can have many projects, which can have many invoices. A payment made by a customer/billToParty can apply to many of the customer's invoices, which must be specified."

然后，应该更容易按customerID跟踪总计，包括已付款和未结发票金额。

Then it should be easier to track totals by customerID, both payments made and invoice amounts outstanding.

这篇关于发票和付款的架构设计-比M：M关系更好的模型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！