什么数据库模式，我可以用它来保存不同类型的计费数据的？ [英] What database schema can I use to save different types of billing data?

问题描述

我有创建一个订单和订单可以宣传为房子账户，发送货到付款（COD），或计入信用卡的系统。我创建了下面的表格：

I have a system that creates an order and that order can be billed to a house account, sent Cash on Delivery (COD), or charged to a credit card. I've created the following tables:

订单

ORDER_ID

billingoption_id

ORDERS
order_id
billingoption_id

BILLINGOPTIONS

billingoption_id

BILLINGOPTIONS
billingoption_id

我不确定如何邻桌应为计费的数据基础之上。我应该建立一个单独的表为每种类型的计费选项（即化学需氧量，信用卡，和众议院账户）？然后我会在订单表，将引用记录计费数据的另一个外键列？

I'm unsure of how the next table should be built for the billing data. Should I build a separate table for each type of billing option (ie. COD, Credit Cards, and House Account)? Then would I have another foreign key column on the Orders table that would refer to a record for the billing data?

推荐答案

您可以做到这一点无论哪种方式：具有字段包含所有的一个大鸣喇叭 billingoptions 表类型，如果为空，对于不适用于特定类型或一堆婴儿表说：明星脱父 billingoptions 表中的字段。两者都有各自的优点和缺点。

You can do it either way: a big honking billingoptions table that has fields that encompasses all of the types, with NULLs for fields that don't apply to a given type, or a bunch of baby tables that "star off" of a parent billingoptions table. Both have their advantages and disadvantages.

有关大鸣喇叭表，

• 这是很好，所有数据都可以很容易地在一个表中被引用。


• 跟踪外键依赖和执行更新或插入是efficent。


• ，但你需要修改表结构，以在将来添加新的结算方式，并有存储（例如，无论是信用卡类型和COD标志在同一个记录被设置数据无效组合的可能性）。

对于小宝宝表，

• 这是很好的数据进行分区，并密切反映了你的程序的对象结构。


• 这是很好，你可以不必担心影响其他人添加新的付款方式，或改变现有的。


• 的关系是非常明确的。你可以不小心与其他存款链接存款，因为外键将要求它与审批挂钩。


• 但是你最终可以引入大量的表到设计，这需要大量的JOIN，可以进行导航痛苦，而且效率不高，当谈到插入和更新。

在工作中，我们结束了与小baby表去。它看起来是这样的：

At work, we ended up going with small baby tables. It looks something like this:

Table Orders:
--> OrderId PK
--> (Lots of Other Fields)

Table Payments:
--> PaymentId PK
--> OrderId (FK) [There may be more than one payment per order]
--> PaymentType [Restricted field contains values like 
       'PAYPAL' or 'CREDIT', you use this to know which 
       baby table to look up that can contain additional 
       information]

Table PaymentsPayPal:
--> PaymentPayPalId PK
--> PaymentId FK points to Table Payments
--> TransactionNo
--> (Other PayPal specific fields)

Table PaymentsCheck:
--> PaymentCheckId PK
--> PaymentId FK points to Table Payments
--> RoutingNo
--> (Other e-check specific fields)

+ other tables for remaining payment types....

所有的付款方式有三个共同的交易相关的表：

All of the payment types share three transaction related tables:

Table PaymentApprovals:
--> PaymentApprovalId PK
--> PaymentId FK points to Table Payments
--> Status [Some flag meaning 'Succeeded', 'Failed', 'Reversed', etc]
--> ProcessorMessage [Something the service sent back, like '(M) CVV2 Matched']
--> Amount
--> (Other administrative fields)

Table PaymentDeposits:
--> PaymentDepositId PK
--> PaymentApprovalId FK points to Table PaymentApprovals
--> Status
--> ProcessorMessage
--> Amount
--> (Other administrative fields)

Table PaymentRefunds:
--> PaymentRefundId PK
--> PaymentDepositId FK points to Table PaymentDeposits
--> Status
--> ProcessorMessage
--> Amount
--> (Other administrative fields)

我们所有的付款方式（信用卡，贝宝，谷歌结帐，检查，现金，点数，和汇票）被抽象成适合这个审批 - >存款 - >退款的比喻，和UI调用在一个 IPayment 和 IPaymentProcessor ​​与不同的实现（接口 Cyber​​sourcePaymentProcessor ​​， PayPalPaymentProcessor ​​等）。抽象一直努力pretty以及在过去一年并在这些不同的方法半，虽然有时图形用户界面将显示不同的措辞给用户（例如，它会说授权和充电，而不是批准和存款信用卡付款，以及进入资金的屏幕在执行一举批准/存款的一步。）

All of our payment methods (Credit Card, PayPal, Google Checkout, Check, Cash, Store Credit, and Money Order) are abstracted to fit into this Approval --> Deposit --> Refund metaphor, and the UI calls the same methods on an IPayment and IPaymentProcessor interfaces with different implementations (CybersourcePaymentProcessor, PayPalPaymentProcessor, etc). The abstraction has worked pretty well over the past year and a half across these disparate methods, although sometimes the GUI will display different verbiage to the user (for example, it'll say "Authorize" and "Charge" instead of "Approve" and "Deposit" for credit card payments, and the screen for entering cash performs the Approve/Deposit step in one fell swoop.)

希望是有道理的。这听起来像你不实际存储支付信息，而是要考虑在这些事情最终会是有益的。

Hope that makes sense. It sounds like you're not actually storing payment information, but it's useful to think about where these things can end up.

这篇关于什么数据库模式，我可以用它来保存不同类型的计费数据的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！