数据库设计：信用充值和交易

Question

我们从事的是潜在客户开发业务。客户从我们这里收到线索。他们查看潜在客户的基本信息并决定是否接受或拒绝该潜在客户。如果他们接受潜在客户，则会扣除 1​​ 个积分（针对每个潜在客户），并且他们可以查看潜在客户的详细信息。

因此，每条线索花费 1 个积分（与一定的货币价值挂钩）。

我们有不同类型的客户：保险代理人、房地产经纪人、信用卡公司、俱乐部等。他们为 1 个积分支付不同的价格。

客户可以预付费或后付费。

我对上述所有内容都有下表：

Customers (id, name, address, is_postpaid, customer_type_id)
CustomerTypes (id, name, credit_price) // name: insurance agent, real estate agent, etc
TransactionTypes (id, name) // add to or deduct from credit balance
CustomerTransactions (id, customer_id, quantity, transaction_type_id, credit_balance, credit_unit_price, date_created)

问题：

• 您对总体方法有何看法？
• 这种设计适合预付费客户。但是后付费呢？

任何反馈和批评都会受到赞赏。

更新：：我已更新上述表格，将 FK 包含在“客户”表格中。

Answer 1

如果交易表的粒度确实是一笔交易（无累积字段），则预付费客户和后付费客户之间没有区别。您可以在同一列中输入正数付款和负数借记，或者（正如某些人喜欢的那样）使用两列表示“正数和负数”客户交易。

客户余额始终可以通过以下方式获得：

select
      CustomerName
    , sum(Credits) - sum(Debits) as AccountBalance
from Transactions as t
join Customer     as c  on c.CustomerId = t.CustomerId
group by CustomerName
order by CustomerName ;

Answer 2

我会将数据库更改为以下内容：

客户（id、名称、customerType_id、地址、is_postpaid）
客户类型（id、名称、信用价格）
CustomerTransactions（id，customer_id，数量，transaction_type，credit_balance，credit_unit_price，date_created）

我不太确定预付费或后付费的概念。如果可以的话请帮我澄清一下。

Answer 3

我还建议预付款和后付款之间没有区别 - 您只是进行交易。

为了适应前后差异，您可以引入一个新表来描述它：

    customer_type
    --------------
    id    
    pre_or_post
    begin_date
    end_date

Answer 4

关于信用的一件事是，从会计的角度来看，如果预付的话，它们就是账面上的负债。它们有点像礼品卡，在使用积分之前您无法确认收入。考虑到这一点，您希望积分在一段时间（例如 1 年）后过期。由于积分过期，您需要按从最旧到最新的顺序扣除积分。

您应该有一个表来记录信用使用情况（即 CustomerCreditUsage），另一表用于记录信用购买情况（CustomerTransactions）。购买后，您检查信用余额是否为负，并从信用使用表中注销记录。如果是预付费的，当您记录信用使用交易时，您将从最早的信用购买的信用余额中扣除。

请记住，信用购买和信用使用之间存在松散的关系。您可以只拥有一个而无需另一个。尽管您不希望在没有匹配信用购买的情况下长时间使用信用（后付费）。