Database schema design for a double entry accounting system? [closed]

Does anybody know or have any links to websites describing details of how to design a database schema for a double entry accounting system ??.

I did find a bunch of articles but non were very explanatory enough. Would appreciate it if someone could help me on this.

Solution 1:

Create the following tables

• account
• transaction
• line_item
• contact (can be a customer a supplier, or an employee).

To keep things simple, we will leave out the account_type table, contact_type table, etc.

Identify the relationships between the tables and set them up

• a contact can have many transactions, but each transaction can only have one contact (one-to-many relationship)
• an account can have many transactions, and one transaction can affect many accounts; line_item is the join table between transaction table and account table (a many-to-many relationship)
• a transaction can have many line items, but each line item must relate to one transaction.

We have the following schema (a one-to-many relationship):

CONTACT ———< TRANSACTION ———< LINE_ITEM >——— ACCOUNT

Add appropriate fields to each table

• Contact
  • contactID
  • name
  • addr1
  • addr2
  • city
  • state
  • zip
  • phone
  • fax
  • email
• Transaction
  • transactionID
  • date
  • memo1
  • contactID
  • ref
• Line_item
  • line_itemID
  • transactionID
  • accountID
  • amount
  • memo2
• Account
  • accountID
  • account_name
  • account_type

Create as many new transactions as needed

For example to add a new transaction in the database, add a new record in the transaction table and fill in the fields, select a contact name, enter a date, etc. Then add new child records to the parent transaction record for each account affected. Each transaction record must have at least two child records (in a double-entry bookkeeping system). If I purchased some cheese for $20 cash, add a child record to the transaction record in the child record, select the Cash account and record −20.00 (negative) in the amount field. Add a new child record, select the Groceries account and record 20.00 (positive) in the amount field. The sum of the child records should be zero (i.e., 20.00 − 20.00 = 0.00).

Create reports in the database based on the data stored in the above tables

The query to give me all records in the database organized so that transaction line item child records are grouped by account, sorted by date then by transaction ID. Create a calculation field that gives the running total of the amount field in the transaction line_items records and any other calculation fields you find necessary. If you prefer to show amounts in debit/credit format, create two calculation fields in the database query have one field called debit, and another called credit. In the debit calculation field, enter the formula "if the amount in the amount field from the line_item table is positive, show the amount, otherwise null". In the credit calculation field, enter the formula "if the amount in the amount field from the line-Item table is negative, show the amount, otherwise null".

Based on this rather simple database design, you can continuously add more fields, tables and reports to add more complexity to your database to track yours or your business finances.

Solution 2:

I figured I might as well take a stab at it. Comments are appreciated – I'll refine the design based on feedback from anyone. I'm going to use SQL Server (2005) T-SQL syntax for now, but if anyone is interested in other languages, let me know and I'll add additional examples.

In a double-entry bookkeeping system, the basic elements are accounts and transactions. The basic 'theory' is the accounting equation: Equity = Assets - Liabilities.

Combining the items in the accounting equation and two types of nominal accounts, Income and Expenses, the basic organization of the accounts is simply a forest of nested accounts, the root of the (minimum) five trees being one of: Assets, Liabilities, Equity, Income, and Expenses.

[I'm researching good SQL designs for hierarchies generally ... I'll update this with specifics later.]

One interesting hierarchy design is documented in the SQL Team article More Trees & Hierarchies in SQL.

Every transaction consists of balanced debit and credit amounts. For every transaction, the total of the debit amounts and the total of the credit amounts must be exactly equal. Every debit and credit amount is tied to one account.

[More to follow ...]