我应该如何对这些简单的金融交易进行建模？
我觉得我在试图构建一个我认为是用于建模基本金融交易的简单数据库模式时碰壁了。我希望你们中一些有更多经验的人能够参与进来并为我指明正确的方向。
我的应用程序有四种不同类型的租赁可供客户购买。因此，每种类型的租赁都有自己的表，并且为了保持引用完整性，每个租赁表都有自己的事务表。
我的原始草图如下所示：
首先，我使用了对函数类型的 FK 引用来避免使用有符号整数。负付款是没有意义的，所以我认为每笔交易都有借方或贷方参考会很有效。这有道理吗？
另一件困扰我的事情是，并非所有交易似乎都是平等的。也就是说，我觉得这个应用程序的交易可能应该被分组到单独的表中。
诸如统一费率费用、可变费率费用、付款、利息和无效等交易是否都应该填入同一张表中？这对我来说似乎很混乱，但我已经被每种租赁类型的交易表所困扰，因此将这些表进一步拆分并不是很有吸引力。
除了付款之外，几乎所有交易类型都将以编程方式构建，因此我可以在“注释”字段中进行引用，该字段指定退回支票或无效交易所引用的付款。这足够好还是我的想法都是错误的？
谢谢！
I feel like I hit a wall trying to structure what I thought would be a simple database schema for modeling basic financial transactions. I'm hoping some of you with more experience can weigh in and point me in the right direction.
My application has four distinctly different types of leases that customers can purchase. As such, each type of lease has it's own table, and in order to keep referential integrity, each lease table has it's own transactions table.
My original sketch looked like this:
First off, I used a FK reference to Function Types to avoid using signed integers. It makes no sense to have a negative payment, so I figured it would work well for each transaction to have either a debit or credit reference. Does this make sense?
Another thing that was bothering me is that all transactions don't appear to be equal. That is, I feel like the transctions for this application should possibly be grouped into separate tables.
Should transactions such as flat rate fees, variable rate fees, payments, interest and voids all be stuffed into the same table? It seems messy to me, but I'm already stuck with a transactions table for each lease type so splitting those tables up even more is not very appealing.
Just about every transaction type other than payments will be built programmatically, so I can have references in the "Notes" field that specifies which payment a bounced check or void transaction is referencing. Is this good enough or am I thinking about this all wrong?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不同类型的租赁有不同的特点吗？如果不是，那么将它们分离到单独的表中不会起到任何作用，并且会增加数据库和编程应用程序的复杂性。
对于所有类型的租赁拥有一个单一的事务表几乎肯定会为您省去以后的麻烦。
我建议您查看数据库答案中提供的一些解决方案。那里有很多金融数据库。您可能会得到一些提示。
Do different types of leases have different features? If not, then separating them out into separate tables accomplishes nothing, and adds to the complexity of both the database and the programmed application.
Having a single transaction table for all types of leases is almost certainly going to save you trouble later on.
I suggest you take a look at some of the solutions presented in Database Answers. There are lots of financial databases there. You might get a few hints.
负数有什么问题？
记录交易类型很好，但如果您坚持所有金额均为正数，那么在尝试计算总数时您就会陷入痛苦的境地。只需将付款和其他收款交易类型存储为正金额，将退款等存储为负金额。这还允许您执行一些操作，例如将由于错误（例如，在客户卡上的错误帐户上扣款）而导致的退款记录为负付款，这与退款不同。
What's wrong with negative amounts?
It's fine to have a record of transaction types, but if you insist on all amounts being positive, you're up for a world of pain when trying to calculate totals. Simply store payments and other money-coming-in transaction types as positive amounts, and refunds, etc. as negative amounts. This also lets you do things like record a charge-back due to a mistake (e.g., charging the wrong account on the customer's card) as a negative payment, which is different to a refund.