发票表设计 [英] Invoice table design

问题描述

我正在为客户创建发票表。有时，他们将不得不向我发送佣金费，或者有时我向他们发送费用（帐户余额）。如何在这种情况下设计发票表？

I am creating an invoice table for the customers. Sometime they will have to send me the commission fees or sometime I send them fees (account balance). How to design a invoice table in this situation?

我想出了这个解决方案，我不知道这是否正确或什么是替代方法？

I have came up with this solution, im not sure if this is correct or what is the alternative way?

tbl_invoice
- invoice_id (PK)
- order_id (FK)
- invoice_date
- amount (copy the price from tbl_order.total table)
- status (Invoice Sent, Cancelled, Amount Received, Amount Sent)


tbl_Payments
 - invoice_id (FK)
 - amount_received (recieved commission fees from customer)
 - amount_sent (sent fees to customer)
 - date_received
 - date_sent

如果tbl_invoice.amount为-30.00，那意味着客户将向我发送费用。

if the tbl_invoice.amount is -30.00 that mean customer will send me the fees.

如果tbl_invoice.amount是30.00，那么我将向客户发送费用。

if the tbl_invoice.amount is 30.00 then I will send the customer the fees.

我需要tbl_invoice.amount字段吗？

Do I need tbl_invoice.amount field?

如果你可以重新设计我的桌子怎么应该是伟大的。

If you could redesign my tables how it should be that be great.

推荐答案

有几件事：

1. 将发票状态标准化为自己的查询表，然后在发票表中放置状态ID，而不是已发送，已取消等。

1. Normalize invoice status to be its own lookup table, then put a Status ID in the invoice table rather than 'Sent', 'Cancelled', etc.

绝对保留发票金额。如果您需要考虑折扣，这可能不同于tbl_order.total中的价格值。

Definitely keep invoice amount. This might have to be different from the price value in tbl_order.total if you ever need to take into account discounts. In any case, numerical data is cheap to store and will be faster to query if you dont have to do any joins.

给付款表格自己的ID列并使其成为PK。

Give the Payments table its own ID column and make it the PK.

其余的看起来不错。有两种表格，一种用于付款，另一种用于付款。如果你真的只需要保存金额和日期信息，我不认为你需要使它更复杂。

The rest looks ok. There is a case for having two tables, one for payments outgoing, and another for payments incoming. If you really only need to keep the amount and date information, then I dont think you need to make it any more complicated.

谢谢，
Chris。

Thanks, Chris.

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