在数据库模式中链接应付账款和总账
2010 年 12 月 13 日，有人提出问题：
我正在寻找总账和应付账款的行业标准逻辑数据模型。有现成的会计数据模型吗？
肯·唐斯回复：
摘录：
最基本的账本是 3 个表：账户、批次和交易。所有交易>必须成批进行。有些人为借方和贷方制作两列，我总是做一列 >，借方和贷方具有相反的符号。
应付账款也非常简单。它的核心是一张供应商表和一张>凭证/发票表。最后生成了一张检查表...之后，根据口味进行修饰:)
由于发票和支票表都会影响总账，我是否正确地假设每个表都需要存储唯一的批号？该架构是否会显示发票：批次和支票：批次表的 1:1 关系？非常感谢您的建议。
A December 13, 2010 a question was asked:
I am looking for industry standard logical data model of general ledger and accounts >payable. Are there any readily available accounting data models?
Ken Downs replied:
Excerpt:
The most basic ledger is 3 tables: Accounts, Batches, and Transactions. All transactions >must be in a batch. Some people make two columns for debit and credit, I always did one >column, with Debits and credits having opposite signs.
Payables is also very simple. At its heart is a table of vendors and a table of >vouchers/invoices. Finally a table of checks generated...After that, embellish to taste :)
Since both the invoice and check table would affect the general ledger, am I correct to assume each would need to store a unique batch number? Would the schema show a 1:1 relationship for the invoice:batch and checks:batch tables? Thank you so much for your advice.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这里不会有任何 1:1 的关系。例如，您始终可以一次性支付多张发票。批处理表的想法是，从会计角度而不是从编程角度来看，它是一个单一的“工作单元”。
批处理没有特殊需要，只是它有助于根据谁做了什么以及何时执行信息来组织信息 - 特别是因为它提供了从业务角度来看一堆事务在逻辑上彼此相关的指示。
Accounts 是一个查找表。事务是事务详细信息，而批处理是事务标题。我同意 @OP 问题中的 Ken Downs 的观点，即单个金额字段就足够了。单独的借方和贷方栏没有意义。这个想法来自纸质会计领域，在所有算术都是手工完成的时代就很有用。在计算机化的场景中，这个想法是不合时宜的，而且实际上会造成比其价值更多的麻烦。我不同意肯·唐斯的观点，因为他的借方和贷方具有相反的符号。在特定账户的情况下确实如此，但根据会计惯例，不同类型的账户的借方金额可能为正数，也可能为负数。资产和收入朝一个方向发展，负债和费用朝另一个方向发展。交易表中的数字是正数还是负数取决于该交易适用于哪种类型的账户。
您需要进行的一项编辑是，当您适当应用借方和贷方时，每个批次都应平衡为零。测试此编辑的逻辑需要知道批次中的每个帐户是否是资产、负债、收入或支出 - 因此这需要成为帐户表的属性。
至于支票、凭证、发票和所有其他的东西——你可能想要，但不一定需要所有这些。拥有它们的原因不是为了严格的帐户余额跟踪，而是为了您可以保留在那里的所有其他指示性信息。您可以将所有这些指示性信息保存在批处理表上的“哑”文本字段中（即“备忘录”）。这就是他们在旧的高脚椅、遮阳板和羽毛笔时代的做法。但是，拥有供应商发票表很方便，因为它可以让您执行方便的操作，例如查询特定供应商的所有发票的列表。这同样适用于其他具体的商业实体，如支票、发票（应收账款）、报表等。
There won't be any 1:1 relationships here. You can always pay multiple invoices in a single batch, for example. The idea of the batch table is that it is a single "unit of work" from an accounting perspective rather than from a programming perspective.
There is no special need for batch except that it is helpful for organizing information in terms of who did what and when - especially insofar as it provides an indication that a bunch of transactions are logically related to one-another from a business perspective.
Accounts is a lookup table. Transactions is the transaction detail, as opposed to Batch which is the transaction header. I agree with Ken Downs from the @OP's question that a single amount field is sufficient. There is no point in separate debit and credit columns. This idea comes from the paper accounting world and is useful from back in the day when all of the arithmetic was done by hand. In a computerized scenario that idea is anachronistic and actually causes more trouble than it's worth. I would disagree with Ken Downs insofar as his debits and credits having opposite signs. This is true within the context of a particular account, but accounts of different types will have debits with either positive or negative amounts according to accounting conventions. Assets and Revenues go in one direction and Liabilities and Expenses go in the other direction. Whether a number is positive or negative in the transaction table will be a matter of which type of account the transaction applies to.
One of your edits will need to be that every batch should balance to zero when you apply debits and credits appropriately. The logic for testing this edit needs to know whether each account in the the batch is an asset, liability, income or expense - so that needs to be an attribute of your account table.
As to checks, vouchers, invoices and all the rest - you probably want, but don't necessarily need all of that. The reason to have them is not for the strict account balance tracking, but rather for all of the other indicative information that you can keep there. You could keep all of this indicative information in a "dumb" text field on the batch table (i.e. "memo"). This is how they did it in the old tall chair, visor and quill days. However, having a vendor invoice table is handy because it lets you do convenient things like querying for a list of all of the invoices from a particular vendor. The same goes for other concrete business entities like cheques, invoices (receivables), statements, etc. etc.