数据库设计：保存不同的付款细节（信用或支票） [英] DB design : save different details of payment (credit or check)

问题描述

我有一个会员可以通过以下三种方式付款：

I have a member that can pay in three different ways:

1. 信用卡


2. 检查


3. 从银行账户转账

如何设计表格来记录其付款类型？

How can I design a table to record their payment type?

对于每种付款类型，必填字段将不同，那么如何设计可以消除空白字段的结构？

For each payment type the required fields would be different, so how can I design a structure that would eliminate the blank fields?

推荐答案

这样做的方法是制作四个表，如果三种方式都有不同的字段。为每个不同的方法和一个表为订单本身做一个表。在订单表中，您将存储orderID和用户用于支付哪种方法的枚举值（除了您需要的任何其他字段之外）。例如，如果您在.NET中进行编程，则可以创建枚举：

The way to do it is to make four tables, if the three ways have all different fields. Make one table for each of the different methods and one table for the order itself. In the order table, you will store the orderID and an enumerated value of which method the user used to pay (in addition to any other fields you need). For instance, if you were programming in .NET, you could create an enum:

Public Enum PaymentMethod
    CreditCard
    Check
    WireTransfer
End Enum

这将有效地设置 CreditCard 等于0，检查到1和 WireTransfer 到2。在您的主订单表中有一个字段，这是什么类型，并将该整数保存在那里。在您的付款表格中，您的主表中有一个用于OrderID的外键。如果需要，您可以将付款记录的ID保留在付款表中，但不必要，因为您可以根据OrderID找到记录：

This would effectively set CreditCard equal to 0, Check to 1 and WireTransfer to 2. Have a field in your main order table for what type this is and save this integer there. In your payment tables, have a foreign key for the OrderID from your main table. If you wanted, you could keep the ID of the record in the payment table, but it's unnecessary because you can find the records based on the OrderID:

SELECT o.*, cc.* FROM Orders o 
INNER JOIN CreditCardPayments cc ON o.OrderID=cc.OrderID
WHERE cc.OrderID=[some number]

当然，如果您有三种不同的方法，那么您必须首先查看以查看哪种付款方式，然后使用适当的 SELECT 语句：

Of course if you have three different methods, you'll have to check first to see which kind of payment it is first and then use the appropriate SELECT statement:

SELECT PaymentMethod FROM Orders WHERE OrderID=[some number]

一旦用方法确定，您可以使用预先制作的 SELECT 从不同的付款方式表中调用的语句。

Once you determine with method, you can use pre-made SELECT statements that call from your different payment method tables.

希望这有帮助！

这篇关于数据库设计：保存不同的付款细节（信用或支票）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！