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

问题描述

我有一个创建订单的系统，该订单可以记入内部账户、货到付款 (COD) 或从信用卡中扣除.我创建了以下表格:

订单
order_id
billingoption_id

账单选项
billingoption_id

我不确定应该如何为计费数据构建下一个表.我是否应该为每种类型的计费选项(即 COD、信用卡和房屋帐户)建立一个单独的表格?那么我会在 Orders 表上有另一个外键列来引用账单数据的记录吗?

解决方案

您可以采用任何一种方式:一个很大的 billingoptions 表，其中包含包含所有类型的字段，字段为 NULL这不适用于给定的类型，或一堆明星"父 billingoptions 表的婴儿表.两者各有优缺点.

对于大喇叭桌，

• 很高兴可以在一个表中轻松引用所有数据.
• 跟踪外键依赖项并执行更新或插入非常有效.
• 但是您需要更改表结构以在将来添加新的计费选项，并且可能会存储无效的数据组合(例如，在同一记录中设置了信用卡类型和 COD 标志).

对于小婴儿桌，

• 数据被分区并密切反映程序的对象结构非常好.
• 很高兴您可以添加新的付款方式或更改现有的付款方式，而不必担心影响其他方式.
• 关系非常明确.您不能意外地将一项存款与另一项存款相关联，因为外键要求将其与批准相关联.
• 但您最终会在设计中引入大量表格，这些表格需要大量 JOIN，导航起来很麻烦，并且在插入和更新方面效率不高.

在工作中，我们最终选择了小型婴儿桌.它看起来像这样:

<前>表订单:--> 订单 ID PK-->(许多其他领域)表付款:--> PaymentId PK--> OrderId (FK) [每个订单可能有多个付款]--> PaymentType [Restricted field contains values like'PAYPAL' 或 'CREDIT'，你用它来知道哪个要查找的婴儿表可以包含额外的信息]表支付贝宝:--> PaymentPayPalId PK--> PaymentId FK 指向表付款--> 交易号-->(其他 PayPal 特定字段)表付款检查:--> PaymentCheckId PK--> PaymentId FK 指向表付款--> 路由号-->(其他电子支票特定字段)+ 剩余付款类型的其他表格....

所有支付类型共享三个交易相关表:

<前>表支付批准:--> PaymentApprovalId PK--> PaymentId FK 指向表付款--> 状态 [某些标志表示成功"、失败"、反转"等]--> ProcessorMessage [服务发回的东西，比如'(M) CVV2 Matched']--> 金额--> (其他行政领域)表支付存款:--> PaymentDepositId PK--> PaymentApprovalId FK 指向表 PaymentApprovals--> 状态--> 处理器消息--> 金额--> (其他行政领域)表支付退款:--> PaymentRefundId PK--> PaymentDepositId FK 指向表 PaymentDeposits--> 状态--> 处理器消息--> 金额--> (其他行政领域)

我们所有的付款方式(信用卡、PayPal、Google Checkout、支票、现金、商店信用和汇票)都被抽象出来以适应此批准 --> 存款 --> 退款比喻，并且 UI 调用IPayment 和 IPaymentProcessor 接口上的相同方法具有不同的实现(Cyber​​sourcePaymentProcessor、PayPalPaymentProcessor 等).在过去的一年半中，抽象在这些不同的方法中运作良好，尽管有时 GUI 会向用户显示不同的措辞(例如，它会说授权"和收费"而不是批准"和信用卡支付入金"，入金界面一举完成审批/入金步骤.

希望这是有道理的.听起来您实际上并未存储付款信息，但考虑一下这些信息最终会出现在哪里是很有用的.

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:

ORDERS
order_id
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?

解决方案

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.

For the big honking table,

• It's nice that all data can easily be referenced in a single table.
• Tracking foreign key dependencies and performing updates or inserts is efficent.
• BUT you need to alter the table structure to add new billing options in the future, and there's the possibility of invalid combinations of data being stored (for example, both a credit card type and a COD flag being set in the same record).

For the small baby tables,

• It's nice that the data is partitioned and reflects your program's object structure closely.
• It's nice that you can add new payment options or alter existing ones without worrying about affecting the others.
• The relationships are VERY explicit. You can't accidentally link a deposit with another deposit, since the foreign key will require that it be linked with an approval.
• BUT you end up introducing a lot of tables into the design, which require lots of JOINs, can be a pain to navigate, and aren't as efficient when it comes to inserts and updates.

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)

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屋！