MySQL复式记账系统数据库设计？
我将在 MySQL 中为复式记账系统创建一个数据库。
我最近读了这篇文章： http://homepages.tcp.co.uk/~m-wigley/ gc_wp_ded.html
我在这篇文章中发现，拥有三个表 ACCOUNT、JOURNAL 和 POSTING 会很方便：
ACCOUNT(accountID, accountName)
JOURNAL(journalID, journalType)
POSTING(postingID, journalID, accountID, amount)
文章描述了如果帐户被借记，则“金额”字段的值将为正值，否则为负值。
现在对于上面的 POSTING 表，我还有其他两个选项。
(1) POSTING(postingID, journalID, accountID, isDr, amount)
在这个选项中，“isDr”字段是一个布尔值。如果帐户被借记，isDr 字段将包含值“true”，否则为“false”。并且“金额”字段始终具有正值。
(2) POSTING(postingID, journalID, accountID, debitAmount, creditAmount)
在这里，如果帐户被借记，我会将金额存储在“debitAmount”字段中，否则我会将其存储在“creditAmount”字段中。
那么，究竟使用上述三个选项中的哪个选项更好？
i am going to create a database for double-entry accounting system in MySQL.
i recently read the article: http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html
i found in this article that it would be convenient to have three tables ACCOUNT, JOURNAL and POSTING:
ACCOUNT(accountID, accountName)
JOURNAL(journalID, journalType)
POSTING(postingID, journalID, accountID, amount)
the article described that if an account is debited, value of the 'amount' field will be positive, else it will be negative.
now for the POSTING table above, i have other two options as well..
(1) POSTING(postingID, journalID, accountID, isDr, amount)
in this option, 'isDr' field is a boolean. if the account is debited, isDr field will contain value "true", otherwise "false". and the 'amount' field will always have a positive value.
(2) POSTING(postingID, journalID, accountID, debitAmount, creditAmount)
here, if the account is debited, i will store amount in 'debitAmount' field, else i will store it in 'creditAmount' field.
so, exactly which option from above three is better to use??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是主观的，但我认为选项 #0（单个金额字段要么为正，要么为负）是最好的。每当您想要进行包含贷方和借方的数学运算时，选项 #1 都会给您带来很大的痛苦，因为您必须查阅
isDr字段来确定是否乘以- 1..选项 #2 没有很好地标准化，因为表达式 debitAmount IS NULL 和 CreditAmount IS NOT NULL 是等价的（如果它们不同，那么您的数据库处于状态不一致）。This is subjective, but I think option #0 (the one where a single
amountfield is either positive or negative) is best. Option #1 will cause you a lot of pain whenever you want to do math that includes both credits and debits, since you'll have to consult theisDrfield to determine whether to multiply by-1. Option #2 is not well-normalized, since the expressionsdebitAmount IS NULLandcreditAmount IS NOT NULLwill be equivalent (and if they ever differ, then your database is in an inconsistent state).选项 #0 有一个我最近遇到的缺点：
在某些情况下，需要写入/记录的金额，并且在分类账的贷方侧也显示为负（-），而不是将其金额显示为正（+）在借方。 （反之亦然）
选项＃0失败（如果没有标记为借方或贷方，或者没有以其他方式解决）
您需要询问您的会计师。 （如果需要记录任何负（-）金额并在贷方查看（反之亦然）。
Option #0 has a draw back which I recently faced with:
There are some cases which require the amount written/recorded and also seen as minus(-) on the credit side on the ledgers, instead of showing its amount as positive(+) at the debit side. (also vice versa)
Option #0 fails in that (if not flagged as debit or credit, or not solved in some another way)
You need to ask this your accountant. (if any minus(-) amount needs to be recored and seen on the credit side (and vice versa).
我推荐选项#1。选项#2 将导致大量未使用的字段（debitAmount 和creditAmount 字段总数的50%）。此外，选项#1 可以让您轻松得出当前余额。
最后（或者也许首先），选项 #1 遵循适当的标准化。
I recommend option #1. Option #2 will resultl in a lot of unused fields (50% of the total quantity of debitAmount and creditAmount fields). Also, Option #1 allows you to easily derive current balances.
Lastly (or perhaps, firstly), option #1 adheres to proper normalization.