SQL数据库设计类型表需要不同的子表吗?

First off, I appreciate any help with this.

首先，我非常感谢你的帮助。

I am struggling to find the best approach to some database design, and I'm not entirely sure where even to start searching for the type of design I am even after.

我正在努力寻找一些数据库设计的最佳方法，我甚至不确定从哪里开始搜索我所追求的设计类型。

In short, we have a bunch of payment method types, for example cash, credit card and paypal. But each of these methods require their own set of parameters both for their setup details (ie information for API details for PayPal) as well as transactions for each method requiring a different set of fields - ie. for credit card - we would want the start date, end date, card number etc.. - for paypal we would want the email address and a bunch of other stuff.

简而言之，我们有很多支付方式类型，例如现金、信用卡和贝宝。但是每一种方法都需要自己的参数集，它们的设置详细信息(即用于PayPal的API细节信息)以及每个需要不同字段的方法的事务(ie)。对于信用卡-我们想要开始日期，结束日期，信用卡号码等。-对于贝宝，我们想要电子邮件地址和其他一些东西。

Now - I could simply have tables: payment_method_types, payment_methods and payments which have all the required fields to cover all bases - for example paypal_email_address field, etc.. but is there a better solution that mimics more of a document-based database whereby you'd just have the relevant fields?

现在——我可以简单地拥有表:payment_method_types、payment_methods和payment，它们具有覆盖所有基的所有必需字段——例如paypal_email_address字段，等等。但是有没有更好的解决方案可以模仿更多的基于文档的数据库，这样你就可以得到相关的字段?

For example the following tables payment_method_types, paypal_payment_methods, cash_payment_methods, paypal_payments, cash_payments etc.. Which doesn't really seem that nice.

例如，下列表payment_method_types、paypal_payment_methods、cash_payment_methods、paypal_payment、cash_payment等。看起来不太好。

There must be a good solution for what it is I want to achieve? Do I just need to include all possible fields (ie. paypal_email_address etc..) and just handle it within my application?

对于我想要达到的目标，一定有一个好的解决方案。我需要包含所有可能的字段吗?paypal_email_address等)并在我的应用程序中处理它?

Thanks very much for any input.

非常感谢您的输入。

2 个解决方案

#1

I could be downvoted by that by relational guys :), but you can store this type of information in JSON (there's good support of JSON in PostgreSQL version 9.3) or hstore columns, it could be good mix of relational database and noSQL style.

我可能会被关系型的人否决:)，但是您可以将这种类型的信息存储在JSON中(在PostgreSQL version 9.3中有很好的JSON支持)或hstore列中，它可以很好地混合使用关系数据库和noSQL样式。

You can store common fields like Amount, Date, Currency and so on in typed columns and have one column of type JSON/hstore to store additional details like card number. So your data could look like:

您可以在类型化列中存储诸如金额、日期、货币等常见字段，并且有一个JSON/hstore类型的列来存储诸如卡号之类的附加细节。所以你的数据可以是:

Type          Date         Amount  Currency  Additional
Credit Card   2013-08-01     15.1  EUR       {Card Number: "XXXX-XXXX-XXXX-XXXX"}
PayPal        2013-07-02    103.8  USD       {Email: "client@company.com"}

or hstore:

或hstore:

Type          Date         Amount  Currency  Additional
Credit Card   2013-08-01     15.1  EUR       Card Number => "XXXX-XXXX-XXXX-XXXX"
PayPal        2013-07-02    103.8  USD       Email => "client@company.com"

Or you can create subtables for additional information - so you store common data in table Payments and store all additional info in PaymentsPayPal, PaymentsCreditCard and so on.

或者您可以为附加信息创建子表——这样您就可以在表支付中存储公共数据，并将所有附加信息存储在PaymentsPayPal、PaymentsCreditCard等中。

#2

This answer can be very complex, but I will go with something simple to inspire only. Something like

这个答案可能非常复杂，但我只会用一些简单的东西来启发。类似的

CreditCardPayment < ActiveRecord::Base
  include PaymentType
  def paypal_some_kind_of_id
    2
  end
end

PaypalPayment < ActiveRecord::Base
  include PaymentType
  def paypal_some_kind_of_id
    1
  end
end

Then, whenever dealing with payments, write only one method, and make sure the method works with both a PaypalPayment and a CreditCardPayment object. Methods that are equal for all classes can go within the PaymentType module, to keep your code dry.

然后，每当处理支付时，只编写一个方法，并确保该方法与PaypalPayment和CreditCardPayment对象同时工作。对所有类都相等的方法可以进入PaymentType模块，以保持代码干燥。

#1