对于分成多个部分的天平来说，哪种桌子设计更好？
我有一个数据库，其中的余额和付款需要分解为不同的“钱桶”以显示它们是如何分配的。例如，有本金、利息、滞纳金、退回支票费用、杂项等。最多有 10 个不同的钱桶。
这两种方法中哪一种是为此设计数据库的更好方法，为什么？
选项 A
PAYMENTS
AccountId
// Other payment-related columns
TotalPaid
PrincipalPaid
InterestPaid
MiscPaid
BadCheckChargesPaid
...
选项 B
PAYMENTS
AccountId
// Other payment-related columns
TotalPaid
PAYMENT_DETAILS
PaymentId
PaymentTypeId
AmountPaid
在大多数情况下，仅使用 1-3 种不同的天平类型
I have a database where the Balance and Payments need to be broken down into different "money buckets" to show how they are allocated. For example, there is principal, interest, late fees, bounced check fees, Misc, etc. There are up to 10 different money buckets.
Which of these two methods is the better way of designing a database for this, and why?
Option A
PAYMENTS
AccountId
// Other payment-related columns
TotalPaid
PrincipalPaid
InterestPaid
MiscPaid
BadCheckChargesPaid
...
Option B
PAYMENTS
AccountId
// Other payment-related columns
TotalPaid
PAYMENT_DETAILS
PaymentId
PaymentTypeId
AmountPaid
In most cases only 1-3 of the different balance types are used
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
选项 B 是更好的规范化、更灵活的选项（稍后可以轻松添加新存储桶），并且会得到我的投票。
Option B is the better normalized, more flexible option (easy to add a new bucket later) and would get my vote.
虽然标准化仙女经常会引诱你朝后者的方向发展（就像我一样），但前者可能更明智。您只讨论了 10 列（而不是 500 列），并且没有真正被破坏的规范化规则。除非这个支付分配桶列表很有可能会增长，否则我会远离 EAV 结构，因为它会产生令人头疼的问题（以及某些查询中的无数连接）。
While the normalization fairy can often tempt you in the direction of the latter (as it does me), the former is probably the more sensible. You're only talking about 10 columns (not 500), and there's no normalization rules that are really being broken. Unless there's the strong possibility that this list of payment allocation buckets will grow, I would stay away from the EAV structure just because of the headaches (and innumerable joins in some queries) that it can produce.
选项 B 对我来说似乎更好。关键在于您的应用程序是否设计为显示这样的详细信息：
如果是这样，规范化版本不仅“更正确”，而且实际上以更接近您的应用程序所需的格式存储数据。
对我来说，最大的问题是您是否将付款总额存储在付款记录中，还是从详细信息中得出。
Option B seems better to me. A clincher would be whether your application is designed to show the details like this:
If so, the normalized version is not only "righter" but actually stores the data in a format closer to what your application requires.
To me, the big question is whether you store the payment total in the payment record or derive it from the details.