On Tuesday, 20 November 2012 07:25:28 UTC-3, Matt Warren wrote:
> > 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<http://ravendb.net/docs/faq/optimizing-referenced-documents-load> (also
> see here<http://ravendb.net/docs/client-api/querying/handling-document-relatio...>)
> to pull in the ContractDetails when displaying an individual transaction,
> but the ContractDetails aren't replicated, they only live in 1 document.
> On Sunday, 18 November 2012 11:30:11 UTC, Jeremy Holt wrote:
>> 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
