如何记录人工支付的发票的概念或方法

Question

我正在尝试设计一种记录人工支付的系统，例如支票，Chaps，Bacs。

我正在为如何连接这些点而苦苦挣扎，从我离开应用程序到返回单击确认这段时间

我有一张发票表，上面有3个供应商。每个3个都有不同的手动支付方法，具有唯一的INVNO。

我今天来了，决定付款，所以我从发票表中提取发票。我得到了一份需要支付的清单。

1. CHEQUE 170 BT
2. CHAPS 300 VIRGIN
3. BACS 10 ORANGE

所以我跑到银行，支付了这些款项，然后返回到我的应用程序，记录下哪3笔付款涵盖了哪张发票。

我希望我输入的支票付款已经完成，在这一点上付款被插入到支付表中，系统将自动分配一个新的PAYID，同时更新发票表与新的PAYID以显示INVNO>PAYID。

问题是输入了支票，分配了新的PAYID "1“，然后转到发票表，并用新的PAYID更新第1,3,6行。

我迷路的地方是，当我输入付款并获得新的ID时，我如何识别付款指的是哪个INVNO？或者我做这件事完全走错了路。

我对如何链接这两个进程感到困惑。获取发票列表，确认哪些发票已支付，而无需手动指定INVNO，因为可能有数百张发票要处理

INVOICE TABLE
INVNO   MERCHANT    T   AMOUNT      METHOD
1       BT              100         CHEQUE
2       VIRGIN          200         CHAPS
3       BT              50          CHEQUE
4       ORANGE          10          BACS
5       VIRGIN          100         CHAPS
6       BT              20          CHEQUE

PAYMENT TABLE
PAYID   MERCHANT    T   AMOUNT
1       CHEQUE          170
2       CHAPS           300
3       BACS            10

INVOICE TABLE
INVNO   MERCHANT    T   AMOUNT      METHOD
1       BT          1   100         CHEQUE
2       VIRGIN      2   200         CHAPS
3       BT          1   50          CHEQUE
4       ORANGE      3   10          BACS
5       VIRGIN      2   100         CHAPS
6       BT          1   20          CHEQUE

Answer 1

另一个由(INVNO, PAYID)对组成的表如何，我们称之为INVOICE_PAYMENT？您可以在将新条目插入PAYMENT之后立即填充它，它将不再需要AMOUNT列(因为可以通过连接表和分组来发现它)；而且现有的两个表都不需要T列(因为可以通过连接INVOICE_PAYMENT来发现它)。

CREATE TABLE INVOICE_PAYMENT (
  INVNO BIGINT UNSIGNED NOT NULL,
  PAYID BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (INVNO, PAYID),
  FOREIGN KEY (INVNO) REFERENCES INVOICE (INVNO),
  FOREIGN KEY (PAYID) REFERENCES PAYMENT (PAYID)
);

ALTER TABLE INVOICE DROP COLUMN T;
ALTER TABLE PAYMENT DROP COLUMN T, DROP COLUMN AMOUNT;

然后，要创建付款，请执行以下操作：

INSERT INTO PAYMENT (MERCHANT) VALUES ('CHEQUE');
SET @pay_id := LAST_INSERT_ID(); -- or fetch into PHP via your API

INSERT INTO INVOICE_PAYMENT (INVNO, PAYID)
  SELECT INVNO, @pay_id FROM INVOICE WHERE METHOD = 'CHEQUE' AND ... ;

要获取给定付款的详细信息，包括已支付的金额：

SELECT PAYMENT.*, SUM(INVOICE.AMOUNT)
FROM   INVOICE
  JOIN INVOICE_PAYMENT USING (INVID)
  JOIN PAYMENT         USING (PAYID)
WHERE  PAYID = ?

要获取给定发票的详细信息，请执行以下操作：

SELECT *
FROM   INVOICE
  JOIN INVOICE_PAYMENT USING (INVID)
  JOIN PAYMENT         USING (PAYID)
WHERE  INVID = ?