3

It has been asked many times before... and no answers with schema.

Could someone share Accounting database schema please? I've read many related articles regarding double-entry accounting and relational database in accounting.. But there is no complete explanation on how complete solution would look like.

Here are the articles I found useful for me:

1) http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html (money movement principles, journal entries)
2) http://compasspointmedia.wordpress.com/2010/04/12/sqldatabase-structure-as-related-to-accounting/ (more deepe explanation on transactions recording, invoices and payments)

Here is one of the questions which are still unclear for me:

We sell products to customer in credit. Debit AR account and credit Sales account. Later we got payment from customer. Its amount was higher than the amount due. How can I handle this unused amount?

Jeffrey Rasmussen
  • 339
  • 2
  • 8
  • 20
  • 1
    Questions such as this should most appropriately be asked of an accountant. Once you have the answer the rest is simple data processing. This is off-topic for SO. –  Oct 13 '13 at 03:35
  • This question appears to be off-topic because it is about accounting practice. –  Oct 13 '13 at 03:35

2 Answers2

6

I made an open source accounting package called Open Accounting. This is what its schema looks like in part:

CREATE TABLE account (id BINARY(16) NOT NULL, orgId BINARY(16) NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, parent BINARY(16) NOT NULL, currency VARCHAR(10) NOT NULL, `precision` INT NOT NULL, debitBalance BOOLEAN NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE transaction (id BINARY(16) NOT NULL, orgId BINARY(16) NOT NULL, userId BINARY(16) NOT NULL, date BIGINT UNSIGNED NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, description VARCHAR(300) NOT NULL, data TEXT NOT NULL, deleted BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY(id)) ENGINE=InnoDB;

CREATE TABLE split (id INT UNSIGNED NOT NULL AUTO_INCREMENT, transactionId BINARY(16) NOT NULL, accountId BINARY(16) NOT NULL, date BIGINT UNSIGNED NOT NULL, inserted BIGINT UNSIGNED NOT NULL, updated BIGINT UNSIGNED NOT NULL, amount BIGINT NOT NULL, nativeAmount BIGINT NOT NULL, deleted BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY(id)) ENGINE=InnoDB;

To explain: you have a table for accounts. These will hold your assets, liabilities, equity, income and expense accounts. You have a table for transactions that represent each accounting journal entry. You have a table called splits which hold the individual debits and credits in the journal entry.

I'll use your example above.

1) Make a sale to a customer on credit:

Debit Assets:Accounts Receivable $100
Credit Income:Sales $100

We insert one row in the transactions table. We insert two rows in the splits table that reference the transactionId. The first split will have the accountId for Assets:Accounts Receivable and amount of 10000 (in pennies). The second split will have the accountId for Income:Sales and amount of -10000. In order for a transaction to be valid the splits must add up to 0. This keeps the basic accounting equation in balance. (Assets = Liabilities + Equity + Income - Expenses).

2) Customer overpays:

Debit Assets:Checking Account $120
Credit Assets:Accounts Receivable $120

3) You now owe the customer a refund of $20 on their overpayment. (You could also recognize it as Income, but that may violate Generally Accepted Accounting Practices).

Debit Assets:Accounts Receivable $20
Credit Assets:Checking Account $20

If you recognize it as income you would do something like this:

Debit Assets:Accounts Receivable $20
Credit Income:Overpayments $20

Patrick Nagurny
  • 61
  • 1
  • 1
2

This book helped me to understand Accounting much better. Hope someone find my answer helpful.

Jeffrey Rasmussen
  • 339
  • 2
  • 8
  • 20