数据库事务 - 或者这是一个规范化问题？ [英] Database transactions - or is this a normalization issue?

问题描述

让我们说一个学生在特定的课程。他的总费用是25000美元（基于其他计算，如学费，杂项费用等）。

现在，这让我很困惑。
如何获得和计算学生的剩余余额。我应该：

1. 每次支付时，从他的总余额（我表中的一列）中减去他支付的金额。 / p>




2. 不要过修改任何列，而使用另一个称为total_fee列的属性。只需从我的tution_payment表中的每个交易中获得他支付的总金额，然后从total_fee中减去结果（这可以通过我的查询来完成）。还请注意，我没有在这里使用total_balance。

我记得有人告诉我

通常只插入金融交易

这是否意味着我以前的解决方案是正确的？目前，我使用后者，但觉得这是一个不准确的解决方案。

我不熟悉数据库如何表示会计交易，所以任何帮助将非常感激。

解决方案

通常，会有一个包含事务的表。学生费用将被预订作为学分的信用和借记给学生。偿还将被预订作为学生的信用。利息费用将贷记给大学。

这样，没有必要存储余额。您可以根据交易记录计算余额。最大的优点是你可以解释平衡;它不只是一个存储在数据库中的数字，但它有它的完整历史。

Lets say a student enrolled in a particular course. His total fee is $25000 (base on other computations such as tuition fee, miscellaneous fees, etc.).

Now, this is what confuses me. How can I get AND compute the total remaining balance of the student. Should I:

1. Subtract the amount he paid from his total balance(a column in my table) every time he pays.

2. Do not ever modify any column, but use another attribute called total_fee column. Just get the total amount he paid from every transactions that can be found in my tution_payment table, and then subtract the result from the total_fee(this can be done through my query). Also please be noted that I didnt use total_balance here.

I remember someone told me that

Financial transactions are generally only inserted

Does this mean that my former solution is right? Currently, I use the latter, but feels that it is an inaccurate solution.

I am not familiar with how databases represent accounting transactions, so any help will be much appreciated.

解决方案

Normally, there would be a table with transactions. The student fee would be booked as credit to the university and debit to the student. A repayment would be booked as credit to the student. An interest fee would be credit to the university.

That way, there is no need to store a balance. You calculate the balance based on the transaction history. The biggest advantage is that you can explain the balance; it's not just a number stored in a database, but it has a full history of how it came to be.

这篇关于数据库事务 - 或者这是一个规范化问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！