Advice needed for modelling double entry bookkeeping

[Jeremy Holt]

Hi,

What would be the correct way to model traditional double entry bookkeeping?

Taking as a starting point, classical accounting “T’s”

 

Transaction (date, other stuff about the transaction)	Bank		Supplier		Sales		Stock		Customer
	Db	Cr	Db	Cr	Db	Cr	Db	Cr	Db	Cr
1 (purchased stock)				100			100
2 (paid supplier)		100	100
3 (sold to customer)						150			150
4 (customer paid)	150									150
Other transactions such as Cost of Goods Sold etc …
Account balances	+50		0		-150		+100		0

 

The requirements are as follows:

1.  Daily Ledger - to be able to query an individual account, i.e. to see all the transactions in the Supplier’s Account, with a daily running balance, i.e.

Supplier’s account
Transaction	Debit	Credit	Balance
Opening Balance	X	Y	Z
1 (2012-3-1)	0	100	-100
2 (2012-4-15)	50	0	-50
3 (2012-4-19)	50	0	0
Totals	100	100	0

 

The Daily Ledger needs to know the opening balance, where the opening balance can be for any date range prior to the date the query is run.

2.  Trial Balance - To be able to query across the entire dataset, to see the total of the Debits, Credits and Balance for each account. Again, the Trial Balance needs to know the opening balance for each account.

Account	Opening Balance	Debits	Credits	Balance
Bank	75	150	100	125
Supplier	0	100	100	0
Sales	-100	0	150	-250
Stock	30	100	0	130
Customer	200	150	150	200

 

I need to create an index that will allow me to query across the entire dataset of about 500,000 individual transactions. There are about 4000 separate accounts. About 50 transactions are added on a daily basis, and I need the ability to occasionally (two or three a month) edit and update individual transactions.

My first attempt at this model is:

public class Transaction{

      public string Id {get;set;}

      public string DebitId {get;set;}

      public decimal DbAmount {get;set;}

      public string CreditId {get;set;}

      public string CrAmount {get;set;}

      public decimal ExchangeRate{get;set;}

      public List<ContractDetail> ContractDetails {get;set;}

}

The dataset looks like this:

Id	DebitId	CreditId	DbAmount	ExchangeRate	CrAmount	Other stuff
1	Stock	Supplier	100	1	100
2	Supplier	Bank	100	1	100
3	Customer	Sales	150	1	150
4	Bank	Customer	150	1	150

 

This model works fine when querying for the “Daily Ledger” however, it requires the accountId to determine the value.

public class GetLedger(string accountId){

        var transactions = session.Query<Transaction>().Where(c=>c.DebitId == “Bank” || c=>c.CredidIt == “Bank”)

        foreach(var tr in transactions){

         var value = accountId == (tr.DebitId ? DbAmount : CrAmount) * ExchangeRate

        }

}

 

However, there is no way to write an index to produce the “Trial Balance” using the dataset in this format.

 

My second attempt at this model is:

 

public class Transaction{

      public string Id {get;set;}

      public string AccountId {get;set;}

      public decimal DbAmount {get;set;}

public decimal CrAmount {get;set;}

      public string OtherAccountId {get;set;}

      public decimal ExchangeRate {get;set;}

      public List<ContractDetail> ContractDetails {get;set;}

}

 

and the dataset now looks like

 

Id	AccountId	DbAmount	CrAmount	OtherAccountId	Exchange rate
1-0	Stock	100	0	Supplier	1
1-1	Supplier	0	100	Stock	1
2-0	Supplier	100	0	Bank	1
2-1	Bank	0	100	Supplier	1
3-0	Customer	150	0	Sales	1
3-1	Sales	0	150	Customer	1
4-0	Bank	150	0	Customer	1
4-1	Customer	0	150	Bank	1

This model works fine, in that it is extremely easy to write the index and query for both the DailyLedger and TrialBalance as well as obtain the OpeningBalances.

 

session.Query<Transaction>().Where(c=>c.AccountId = accounted).Sum(c=>DbAmount) etc

 

However, the data is essentially duplicated for each pair of transactions. This causes potential problems in synchronizing the “support data”, i.e. the list of ContractDetail, payment date etc as well as complicating the data entry form.

 

The numbers used here do not take into account the exchange rate that further complicate matters.

 

I think that both models are fundamentally flawed, the first one needs logic to obtain the result and only works for the Daily Ledger, while the second one is simpler from the point of view of obtaining the results for both the Daily Ledger and the Trial Balance but the data is duplicated and requires synchronization.

 

I would add that prior to rewriting the application, I used the first model with SqlServer, and now that I’m rewriting the app I use the second model with RavenDb.

 

Does anyone have any suggestions as how to correctly model this data?

 

Many thanks in advance

Jeremy

 

[Jeremy Holt]

This is the data entry form

[Matt Warren]

> However, the data is essentially duplicated for each pair of transactions. This causes potential problems in 

> synchronizing the “support data”, i.e. the list of ContractDetail, payment date etc as well as complicating 

> the data entry form.

To solve the issue with the 2nd approach, why don't you store a reference to the ContractDetails and have that live in a separate doc, like so:

public class Transaction

{
        public string Id {get;set;}
        public string AccountId {get;set;}
        public decimal DbAmount {get;set;}
        public decimal CrAmount {get;set;}
        public string OtherAccountId {get;set;}
        public decimal ExchangeRate {get;set;}

        public List<string> ContractDetailIds {get;set;}
}

Then you can use the RavenDB Include feature (also see here) to pull in the ContractDetails when displaying an individual transaction, but the ContractDetails aren't replicated, they only live in 1 document.

[Jeremy Holt]

Matt,

Thanks so much for the suggestion - that's exactly what I'm going to do.

I've been struggling with this problem for at least two months - not once did it occur to me to take your approach!

Thanks again

Jeremy