Linking payables and general ledger in the database schema

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 De开发者_运维百科bits 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.

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.