Re: [sqlite] Double entry bookkeeping

[Kees Nuyt]

On Thu, 5 Mar 2009 09:35:46 -0600, Alan Cohen
 wrote:

>What you have laid out as a schema might me a bare minimum for most
>purposes.  I've spent most of the last 30 years configuring and implementing
>financial systems, mostly General Ledger for a variety of clients and
>developers.  Here are the top group of items I think you have missed:

[snip]

Excellent article!
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[Alan Cohen]

What you have laid out as a schema might me a bare minimum for most
purposes.  I've spent most of the last 30 years configuring and implementing
financial systems, mostly General Ledger for a variety of clients and
developers.  Here are the top group of items I think you have missed:
- multiple entities.  This could be as simple as 'my money' and 'my
company's money'.  They are distinct and common enough to be included in any
accounting system.  And, since you may need to record movements between
entities, you will probably want this on the transaction table.
- departments, product lines, etc. Any groupings within an entity to
separate who is responsible for a transaction.  This could be in a retail
environment like grocery, frozen foods, produce and meat departments.  Think
of them as another way to slice and dice the collected information into
reports.
- account type.  I have never seen an accounting system without some method
of separating Assets, Liabilities, Equity, Revenue, and Expense accounts.
This can drive how you display a number in a report (liabilities, equity and
revenue all have normally credit [negative numbers, by convention] balances,
while assets and expenses all have normally debit balances.  In addition,
revenue and expense accounts do not carry balances forward past fiscal year
ends while the other three do.  Some systems expand on this basic idea to
create many more types such as bank, AR, Fixed Asset, ContraFixed Asset,
etc.  This is generally implemented on the Account table as a single
character field, validated by a separate table of valid values (very small)
- currency.  Most places and people will at some time need to record
transactions in a currency other than their home currency.  There is an
international list of 3 character codes you should be able to find on the
web andjust download.  This may be as simple as taking a vacation to another
country.  This affects the transaction table and also ends up needing some
kind of exchange rate table that relates the two currencies with a rate for
that day (also available on the web).  Also recognize that accounting
standards (GAAP, IFRS, and various government agencies) require the use of
different exchange rates for different kinds of conversions.  Another reason
for account type.
- calendar.  Any accounting is period-driven - what you define as a period
is largely up to you.  But, the concept of fiscal year is deeply ingrained
in our laws.  Some countries go so far as to mandate a fiscal year for all
companies within their borders.  In the US, the basic period for external
financial reporting (for listed stock companies) is the quarter.  Most
companies also divide the year into 12-14 periods, which sum to the
quarters.  Monthly periods are most common, with 13 4-week periods as a
common alternative, often in retailing.  And both versions may have an
adjustment period at year-end to distinguish special entries.  Banks are a
special case, requiring daily accounting periods and the calculation of
something called 'average daily balance' [and another table to it] (but you
aren't likely to be developing a banking system!).
- ledger.  One of the most important features of any accounting system is
the recognition that the effort to sum all relevant transactions each time
you want to know an account balance is time and processing resource
consuming.  So, every accounting system I've seen provides a table, often
just called Ledger, with basically the following columns:
k  entity
k  accounting year
k  accounting period (within year)
k  account
k (any other groupings like departments or product lines)
k currency
   balance (Debits are positive, Credits are negative) [in some countries,
especially in Europe, the law requres separate balance fields are required
for Debits and Credits.  These countries also have negative debits and
positive credits, further complicating the transaction table.]
('k' indicates part of a key; some people simply add a unique Id as the key
and then use these fields for indexing.)

This Ledger table provides summary information that can quickly be used to
answer questions from management and owners  This table can be many times
smaller than the transaction table; 1 million transactions/month summarized
to 10,000 Ledger rows is not unusual (although this would be extremely huge
for SQLite).

This also implies a process to sum all new transactions for an
entity/account/othergrouping combination affecting a period and adding it to
any pre-existing balance (if any) or inserting the row if there was none
previously.  Generally, this is called 'Posting' the transactions.

- unique id.  Some countries, including Mexico (in my experience), require a
unique id for any transaction that extends beyond the entity, like a reciept
given out to a customer.  This must be tracked in the transaction table, if
you are in an affected country.

Now, I don't know what you are trying to use this schema to accomplish, but
I hope I've been 

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi all,

Below the basic SQL schema I have so far for double entry bookkeeping.

Does this look like a viable schema? I think I'm interpreting the  
general structure of double entry bookkeeping and earlier discussion  
here correctly. I welcome any comments specifically on the schema.

Basically each Transaction can have multiple Entries and each Entry  
refers to one Account. An Entry may have an optional Narrative, stored  
in the "Entries Narrative" table. Each Entry may have none, one or  
more References, such as invoice numbers, order numbers, cheque  
numbers, stored in the "Entries References" table.

create table Accounts
(
  ID integer
primary key
, Code text
collate nocase
unique
not null
references "Entries" ("Account Code")
, Name text -- brief name
collate nocase
)
;
create table "Transactions"
(
  ID integer
primary key
references "Entries" ("Transaction ID")
, Date date -- julianday of the 
date that the transaction occurred
)
;
create table "Entries"
(
  ID integer
primary key
references "Entries Narrative" (ID)
, "Transaction ID" integer
references Transactions (ID)
on delete cascade
on update cascade
, "Account Code" text
collate nocase
references Accounts (Code)
on delete restrict
on update cascade
, Amount integer-- amount in cents, 
positive or negative
)
;
create table "Entries References"   -- Optional reference(s) for 
each  
Entry
(
  ID integer
primary key
, "Entry ID" integer
references Entries (ID)
on delete cascade
on update cascade
, "Reference" text  -- internal or external 
reference such as  
invoice or cheque number
collate nocase
not null
)
;
create table "Entries Narrative"-- Optional description for 
each Entry
(
  ID integer
primary key
references "Entries" (ID)
on delete cascade
on update cascade
, Narrative text
not null
)
;

-- To prevent deletion of Transactions and Entries:

create trigger "Entries delete"
before delete
on "Entries"
begin
select raise(rollback, 'You cannot delete Entries. You must instead  
reverse out the Entries.');
end
;

-- And here is a view showing all the Entries grouped by Transaction:

create view "Entries Report"
as
select
  Entries.ID as ID
, Transactions.ID as "Transaction ID"
, date(Transactions.Date, '0.1 seconds', 'localtime') as Date
, case when Amount < 0 then round(-Amount/100.0,2) end as Debit
, case when Amount >= 0 then round(Amount/100.0,2) end as Credit
, Accounts.Code
, Accounts.Name
, Narrative
, group_concat("Reference", ', ') as "References"
from Entries
left join "Entries Narrative" on Entries.ID = "Entries Narrative".ID
left join "Entries References" on Entries.ID = "Entries  
References"."Entry ID"
left join Transactions on Entries."Transaction ID" = Transactions.ID
left join Accounts on Entries."Account Code" = Accounts.Code
group by "Transaction ID", ID
;

I realize that the foreign keys (eg references ... on delete restrict)  
aren't currently implemented by SQLite, but they do parse and can be  
implemented by triggers, such as via the genkey utility:
http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README

By the way, is there a way to post colored text to this mail list? The  
above schema is a lot easier to read in color.

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Stanton]

BareFeet wrote:
> Hi John,
> 
>> You still miss the point of the cross reference ID.  It is NOT the
>> reference ID od the document, such as an invoice or check number,  
>> but it
>> more like the row ID used by Sqlire as a unique key for a DB row.
> 
> I thought, from your explanation, that was what you were using the  
> "Reference ID" for, as you said:
> 
>>> reference ID to tag the particular complete transaction
> 
> Anyway, it doesn't matter since I think we're talking about the same  
> thing, but with different column names. If you want to clarify, it  
> would help if you posted a schema of the three tables and the  
> relationships between them.
> 
>> You could normalize out the date and have stored as associated with  
>> the cross reference.  The cost of that would be an extra row lookup  
>> each time you want to get the date of an accounting transaction.  A  
>> performance and code complexity decision.
> 
> It's also an SQL design issue which impacts performance and the  
> ability to extract different reports. De-normalized databases are  
> fundamentally a bad thing. You wouldn't, for instance, design a  
> meeting database where every meeting event explicitly store the name  
> and contact details of the person you were meeting. You'd store it in  
> a People table and refer each event to it. Linking the data through  
> "an extra row lookup" is trivial and optimized within the SQL engine.
Actually an extra row lookup is not trivial, and can be a mighty burden. 
  Normalization is a trade off between function and performance, and one 
where individual circumstances drive the compromises.
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Machin]

On 20/02/2009 12:35 PM, BareFeet wrote:
> Hi John (Machin),
> 
> Thanks for the discussion.
> 
>>> I understand that double entry bookkeeping traditionally uses the  
>>> redundancy as an error check, but that seems more appropriate for  
>>> manual paper systems, since computer systems can validate entries  
>>> automatically.
>> That's just fine and dandy at data entry time. However do please  
>> consider that one ill-considered action by a support programmer can  
>> cause a whole lot more damage a whole lot faster than a book-keeper  
>> with a green eyeshade and a quill pen. Redundancy is *GOOD*. It lets  
>> you detect errors. With sufficient redundancy you may even be able  
>> to correct errors.
> 
> True, but redundancy within an SQL schema is a bad thing.

Excessive unthinking redundancy is bad. So is excessive unthinking 
normalisation.

> There are  
> other ways to cater for redundancy, such as backups

Really useful. How long are they kept? How long will it take to get 
access to an old backup? Will it be readable? Does your SQL allow INNER 
JOIN old_backup.critical_table ON ...?


> and other ways to  
> prevent entry errors such as triggers and contraints.

As I said, data entry time is not the problem. In the real world, 
support programmers will turn off the constraints in order to do 
maintenance. If you are lucky, they will remember to turn the 
constraints back on after they have had their wicked way with the database.

> The SQL schema  
> should be a tight normalized model.
> 
> Having said that, however, I think that storing the Amount of each  
> Entry within a Transaction is inevitable from a design perspective.

Of course. It's screamingly obviously an utter nonsense to pursue 
normalisation that far.

> I  
> just wondered if anyone had come up with a design that is fully  
> normalized.

See "nonsense" above.


> 
>>> All Accounting Entries in the same Transaction should sum their  
>>> Amounts to be zero.
>> Oh. I thought you wanted to store only n-1 amounts and  
>> "deduce" (i.e. guess) the last one.
> 
> It was more of a wonder than a want ;-) Since every stored double  
> entry transaction will sum its Amounts to zero, we can deduce the nth  
> Amount by knowing the other Amounts, which technically violates  
> normalization (Third Normal Form). So I wondered if anyone had come up  
> with a schema that practically implemented this. I presume not, so  
> will continue with the n Amounts stored for each Transaction.
> 
>> Some people would go into severe shock-horror mode at the suggestion  
>> of
>> "updating" the transaction date after it was entered. They would  
>> prefer
>> to reverse out [not delete!] the presumably erroneous transaction and
>> write a correct one.
> 
> Do you mean, like an audit trail of changes? 

Yes, but not as a separate table or as something external to the 
database. I mean within the same table; like I said, no update, no delete.

> That's fine. I am just  
> boiling the schema down to the bare essentials here to focus  
> discussion.

An audit trail *is* a bare essential.

> Catering for logging of changes is a great idea and  
> practice, but just outside the scope of the basic schema to which I'm  
> referring. Whether an update initiates an overwrite of data or a  
> "reverse out", it would still have to be done for each Date  
> redundantly, if the Date was stored for each Entry within a  
> transaction. Since by definition all Entries in a Transaction occur on  
> the same Date, it would be best to store it once once for that  
> Transaction.
> 
>> Concerns about "wasted storage" were understandable when a DASD held
>> (say) 50Mb and was the size of a washing machine. That's a long time  
>> ago.
> 
> Again, it's not a case of "we have space so let's waste it". That  
> would be like storing a person's contact details in every event in am  
> Event table.

A screamingly obvious strawman.

> It doesn't matter if you have a terabyte of space, it's  
> just bad design, creates many problems and defeats the whole purpose   
> of a relational database.
> 
> For reference to Normalization, see here:
>  id=VsZ5bUh0XAkC=PA65=PA63#PPA63,M1>
> 
>>> I found this:
>>> Modeling Business Rules: Data Driven Business Rules
>>> http://www.tdan.com/view-articles/5227
>>> which, seems similar to the route I was taking. If I'm interpreting  
>>> it correctly, the TDAN article suggests a relationship between  
>>> Accounting Entry and Accounting Transaction:
>>>
>>> create table "Accounting Entry"
>>> (
>>>   ID
>>> , "Transaction ID"  --> "Accounting Transaction".ID
>>> , "Account ID"  --> Account.ID
>>> , Amount
>>> , Operator  -- plus or minus
>>> )
>>> ;
> 
> 
>> Do you mean Amount is always non-negative and Operator tells you  
>> whether it is DR or CR?
> 
> That schema was my interpretation of the design discussion page above.  
> There, yes, they use Operator as plus or 

Re: [sqlite] Double entry bookkeeping

[John Stanton]

We are talking about different things.  The referennce ID is an ID 
assigned by the originator or the transaction, say an invoice number 
from a vendor or a check number from a bank whereas the cross reference 
is assigned by the application software and represents a key in an index 
on the table.

The points made by another poster are significant - not only is the 
design of an accounting database important from a technical perspective, 
it must also not violate the Doctrines of Accounting(consistency, 
disclosure etc).  Since transactions are a matter of legal record they 
cannot be changed or deleted, only added, and it should be possible mto 
prove that they cannot be changed.  The locally assigned cross reference 
number tagging the transaction set can also be used as an audit tool to 
prove the absence of deletions.


BareFeet wrote:
> Hi John,
> 
>> You still miss the point of the cross reference ID.  It is NOT the
>> reference ID od the document, such as an invoice or check number,  
>> but it
>> more like the row ID used by Sqlire as a unique key for a DB row.
> 
> I thought, from your explanation, that was what you were using the  
> "Reference ID" for, as you said:
> 
>>> reference ID to tag the particular complete transaction
> 
> Anyway, it doesn't matter since I think we're talking about the same  
> thing, but with different column names. If you want to clarify, it  
> would help if you posted a schema of the three tables and the  
> relationships between them.
> 
>> You could normalize out the date and have stored as associated with  
>> the cross reference.  The cost of that would be an extra row lookup  
>> each time you want to get the date of an accounting transaction.  A  
>> performance and code complexity decision.
> 
> It's also an SQL design issue which impacts performance and the  
> ability to extract different reports. De-normalized databases are  
> fundamentally a bad thing. You wouldn't, for instance, design a  
> meeting database where every meeting event explicitly store the name  
> and contact details of the person you were meeting. You'd store it in  
> a People table and refer each event to it. Linking the data through  
> "an extra row lookup" is trivial and optimized within the SQL engine.
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John (Machin),

Thanks for the discussion.

>> I understand that double entry bookkeeping traditionally uses the  
>> redundancy as an error check, but that seems more appropriate for  
>> manual paper systems, since computer systems can validate entries  
>> automatically.
>
> That's just fine and dandy at data entry time. However do please  
> consider that one ill-considered action by a support programmer can  
> cause a whole lot more damage a whole lot faster than a book-keeper  
> with a green eyeshade and a quill pen. Redundancy is *GOOD*. It lets  
> you detect errors. With sufficient redundancy you may even be able  
> to correct errors.

True, but redundancy within an SQL schema is a bad thing. There are  
other ways to cater for redundancy, such as backups, and other ways to  
prevent entry errors such as triggers and contraints. The SQL schema  
should be a tight normalized model.

Having said that, however, I think that storing the Amount of each  
Entry within a Transaction is inevitable from a design perspective. I  
just wondered if anyone had come up with a design that is fully  
normalized.

>> All Accounting Entries in the same Transaction should sum their  
>> Amounts to be zero.
>
> Oh. I thought you wanted to store only n-1 amounts and  
> "deduce" (i.e. guess) the last one.

It was more of a wonder than a want ;-) Since every stored double  
entry transaction will sum its Amounts to zero, we can deduce the nth  
Amount by knowing the other Amounts, which technically violates  
normalization (Third Normal Form). So I wondered if anyone had come up  
with a schema that practically implemented this. I presume not, so  
will continue with the n Amounts stored for each Transaction.

> Some people would go into severe shock-horror mode at the suggestion  
> of
> "updating" the transaction date after it was entered. They would  
> prefer
> to reverse out [not delete!] the presumably erroneous transaction and
> write a correct one.

Do you mean, like an audit trail of changes? That's fine. I am just  
boiling the schema down to the bare essentials here to focus  
discussion. Catering for logging of changes is a great idea and  
practice, but just outside the scope of the basic schema to which I'm  
referring. Whether an update initiates an overwrite of data or a  
"reverse out", it would still have to be done for each Date  
redundantly, if the Date was stored for each Entry within a  
transaction. Since by definition all Entries in a Transaction occur on  
the same Date, it would be best to store it once once for that  
Transaction.

> Concerns about "wasted storage" were understandable when a DASD held
> (say) 50Mb and was the size of a washing machine. That's a long time  
> ago.

Again, it's not a case of "we have space so let's waste it". That  
would be like storing a person's contact details in every event in am  
Event table. It doesn't matter if you have a terabyte of space, it's  
just bad design, creates many problems and defeats the whole purpose   
of a relational database.

For reference to Normalization, see here:


>> I found this:
>> Modeling Business Rules: Data Driven Business Rules
>> http://www.tdan.com/view-articles/5227
>> which, seems similar to the route I was taking. If I'm interpreting  
>> it correctly, the TDAN article suggests a relationship between  
>> Accounting Entry and Accounting Transaction:
>>
>> create table "Accounting Entry"
>> (
>>ID
>>  , "Transaction ID"  --> "Accounting Transaction".ID
>>  , "Account ID"  --> Account.ID
>>  , Amount
>>  , Operator  -- plus or minus
>> )
>> ;


> Do you mean Amount is always non-negative and Operator tells you  
> whether it is DR or CR?

That schema was my interpretation of the design discussion page above.  
There, yes, they use Operator as plus or minus with always a positive  
Amount. I think that's unnecessary and confusing, so I would instead  
opt for an Amount that can be positive or negative, eliminating the  
Operator column. The data can still be presented to the user in Debit  
and Credit columns that only have positive values, but the storage can  
be signed. Any thoughts/agreement/disagreement on that?

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John,

> You still miss the point of the cross reference ID.  It is NOT the
> reference ID od the document, such as an invoice or check number,  
> but it
> more like the row ID used by Sqlire as a unique key for a DB row.

I thought, from your explanation, that was what you were using the  
"Reference ID" for, as you said:

>> reference ID to tag the particular complete transaction

Anyway, it doesn't matter since I think we're talking about the same  
thing, but with different column names. If you want to clarify, it  
would help if you posted a schema of the three tables and the  
relationships between them.

> You could normalize out the date and have stored as associated with  
> the cross reference.  The cost of that would be an extra row lookup  
> each time you want to get the date of an accounting transaction.  A  
> performance and code complexity decision.

It's also an SQL design issue which impacts performance and the  
ability to extract different reports. De-normalized databases are  
fundamentally a bad thing. You wouldn't, for instance, design a  
meeting database where every meeting event explicitly store the name  
and contact details of the person you were meeting. You'd store it in  
a People table and refer each event to it. Linking the data through  
"an extra row lookup" is trivial and optimized within the SQL engine.

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Stanton]

You still miss the point of the cross reference ID.  It is NOT the 
reference ID od the document, such as an invoice or check number, but it 
more like the row ID used by Sqlire as a unique key for a DB row.

You could normalize out the date and have stored as associated with the 
cross reference.  The cost of that would be an extra row lookup each 
time you want to get the date of an accounting transaction.  A 
performance and code complexity decision.  As they say "you pays your 
money and you takes your choice".

BareFeet wrote:
> Hi John,
> 
> Thanks again for your discussion.
> 
>> "Double Entry" book keeping is actually a misnomer.  A transaction  
>> is very likely to have more than two entries.
> 
> Yes, I realize that, though most Transactions generally have just two.  
> In any case, if there are n entries in a Transaction, we only need n-1  
> of the Entry amounts in order to deduce the remaining amount. SQL (or  
> more generally, set theory) normalization suggests that explicitly  
> storing a redundant record (in this case, the nth amount) is not  
> ideal. I understand that double entry bookkeeping traditionally uses  
> the redundancy as an error check, but that seems more appropriate for  
> manual paper systems, since computer systems can validate entries  
> automatically. So I wondered if there's a normalized approach to  
> double entry bookkeeping.
> 
>> The "transaction ID" I mentioned is a locally generated reference ID  
>> to tag the particular complete transaction, which might include  
>> postings to a bank account, sales, cost of sales, inventory etc.  It  
>> is not conventionally used, but confers great advantages when it is  
>> implemented.  It lets you locate the balancing entries for any  
>> accounting transaction.  In other words given any element of an  
>> accounting transaction you can complete the T Account.
> 
> I think you actually called this the "Cross Reference" and I called it  
> "Transaction ID". But, yes, I understand using it in each Accounting  
> Entry that is part of the same Transaction, in order to group them  
> together. All Accounting Entries in the same Transaction should sum  
> their Amounts to be zero.
> 
>>> You seem to suggest storing the date for each entry within a  
>>> transaction. Would it not be better to store it once only for the  
>>> transaction as a whole, since each entry will share that same date?
> 
>> Dating each accounting transaction aids in creating a unique ID.
> 
> I understand the need to date a Transaction as a whole (therefore  
> storing the Date in the Transaction table), but it seems redundant and  
> denormalized to store the same date in each constituent Accounting  
> Entry. It doesn't make any of the Accounting Entries more unique  
> (since they all by nature have the same date).
> 
>> Normalizing it out is nor necessarily an advantage.
> 
> I can see disadvantages of requiring multiple updating (changing the  
> date in one Accounting Entry requires updating the Date for all other  
> Accounting Entries that are part of the same Transaction), wasted  
> storage etc. I can't see any advantages.
> 
> Or perhaps you actually also meant to imply that Date should be stored  
> once for the Transaction (not for each Entry within it)?
> 
>> It has been my observation that accounting data is generally stored  
>> in poorly thought-out data structures which then require layer upon  
>> layer of logic to overcome the deficiencies.
> 
> Thanks then for your insight into the matter :-) I want to get it  
> right from the outset. In my experience poor normalization  
> necessitates layers of fixes down the road.
> 
> Here is the basic schema from before. I welcome any direct suggestions  
> about the schema:
> 
>>> create table "Accounting Entry"
>>> (
>>>   ID
>>> , "Transaction ID"  --> "Accounting Transaction".ID
>>> , "Account ID"  --> Account.ID
>>> , Amount
>>> , Operator  -- plus or minus
>>> )
>>> ;
>>> create table "Accounting Transaction"
>>> (
>>>   ID
>>> , Date
>>> , Description
>>> )
>>> ;
>>> create table Account
>>> (
>>>   ID
>>> , Description
>>> )
>>> ;
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Machin]

On 20/02/2009 9:23 AM, BareFeet wrote:
> Hi John,
> 
> Thanks again for your discussion.
> 
>> "Double Entry" book keeping is actually a misnomer.  A transaction  
>> is very likely to have more than two entries.
> 
> Yes, I realize that, though most Transactions generally have just two.  
> In any case, if there are n entries in a Transaction, we only need n-1  
> of the Entry amounts in order to deduce the remaining amount. SQL (or  
> more generally, set theory) normalization suggests that explicitly  
> storing a redundant record (in this case, the nth amount) is not  
> ideal. I understand that double entry bookkeeping traditionally uses  
> the redundancy as an error check, but that seems more appropriate for  
> manual paper systems, since computer systems can validate entries  
> automatically.

That's just fine and dandy at data entry time. However do please 
consider that one ill-considered action by a support programmer can 
cause a whole lot more damage a whole lot faster than a book-keeper with 
a green eyeshade and a quill pen. Redundancy is *GOOD*. It lets you 
detect errors. With sufficient redundancy you may even be able to 
correct errors.

> So I wondered if there's a normalized approach to  
> double entry bookkeeping.
> 
>> The "transaction ID" I mentioned is a locally generated reference ID  
>> to tag the particular complete transaction, which might include  
>> postings to a bank account, sales, cost of sales, inventory etc.  It  
>> is not conventionally used, but confers great advantages when it is  
>> implemented.  It lets you locate the balancing entries for any  
>> accounting transaction.  In other words given any element of an  
>> accounting transaction you can complete the T Account.
> 
> I think you actually called this the "Cross Reference" and I called it  
> "Transaction ID". But, yes, I understand using it in each Accounting  
> Entry that is part of the same Transaction, in order to group them  
> together. All Accounting Entries in the same Transaction should sum  
> their Amounts to be zero.

Oh. I thought you wanted to store only n-1 amounts and "deduce" (i.e. 
guess) the last one.

> 
>>> You seem to suggest storing the date for each entry within a  
>>> transaction. Would it not be better to store it once only for the  
>>> transaction as a whole, since each entry will share that same date?
> 
>> Dating each accounting transaction aids in creating a unique ID.
> 
> I understand the need to date a Transaction as a whole (therefore  
> storing the Date in the Transaction table), but it seems redundant and  
> denormalized to store the same date in each constituent Accounting  
> Entry. It doesn't make any of the Accounting Entries more unique  
> (since they all by nature have the same date).
> 
>> Normalizing it out is nor necessarily an advantage.
> 
> I can see disadvantages of requiring multiple updating (changing the  
> date in one Accounting Entry requires updating the Date for all other  
> Accounting Entries that are part of the same Transaction), wasted  
> storage etc. I can't see any advantages.

Some people would go into severe shock-horror mode at the suggestion of 
"updating" the transaction date after it was entered. They would prefer 
to reverse out [not delete!] the presumably erroneous transaction and 
write a correct one.

Concerns about "wasted storage" were understandable when a DASD held 
(say) 50Mb and was the size of a washing machine. That's a long time ago.

> 
> Or perhaps you actually also meant to imply that Date should be stored  
> once for the Transaction (not for each Entry within it)?
> 
>> It has been my observation that accounting data is generally stored  
>> in poorly thought-out data structures which then require layer upon  
>> layer of logic to overcome the deficiencies.
> 
> Thanks then for your insight into the matter :-) I want to get it  
> right from the outset. In my experience poor normalization  
> necessitates layers of fixes down the road.
> 
> Here is the basic schema from before. I welcome any direct suggestions  
> about the schema:
> 
>>> create table "Accounting Entry"
>>> (
>>>   ID
>>> , "Transaction ID"  --> "Accounting Transaction".ID
>>> , "Account ID"  --> Account.ID
>>> , Amount
>>> , Operator  -- plus or minus

Do you mean Amount is always non-negative and Operator tells you whether 
it is DR or CR?


>>> )
>>> ;
>>> create table "Accounting Transaction"
>>> (
>>>   ID
>>> , Date
>>> , Description
>>> )
>>> ;
>>> create table Account
>>> (
>>>   ID
>>> , Description
>>> )
>>> ;
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

___
sqlite-users mailing 

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John,

Thanks again for your discussion.

> "Double Entry" book keeping is actually a misnomer.  A transaction  
> is very likely to have more than two entries.

Yes, I realize that, though most Transactions generally have just two.  
In any case, if there are n entries in a Transaction, we only need n-1  
of the Entry amounts in order to deduce the remaining amount. SQL (or  
more generally, set theory) normalization suggests that explicitly  
storing a redundant record (in this case, the nth amount) is not  
ideal. I understand that double entry bookkeeping traditionally uses  
the redundancy as an error check, but that seems more appropriate for  
manual paper systems, since computer systems can validate entries  
automatically. So I wondered if there's a normalized approach to  
double entry bookkeeping.

> The "transaction ID" I mentioned is a locally generated reference ID  
> to tag the particular complete transaction, which might include  
> postings to a bank account, sales, cost of sales, inventory etc.  It  
> is not conventionally used, but confers great advantages when it is  
> implemented.  It lets you locate the balancing entries for any  
> accounting transaction.  In other words given any element of an  
> accounting transaction you can complete the T Account.

I think you actually called this the "Cross Reference" and I called it  
"Transaction ID". But, yes, I understand using it in each Accounting  
Entry that is part of the same Transaction, in order to group them  
together. All Accounting Entries in the same Transaction should sum  
their Amounts to be zero.

>> You seem to suggest storing the date for each entry within a  
>> transaction. Would it not be better to store it once only for the  
>> transaction as a whole, since each entry will share that same date?

> Dating each accounting transaction aids in creating a unique ID.

I understand the need to date a Transaction as a whole (therefore  
storing the Date in the Transaction table), but it seems redundant and  
denormalized to store the same date in each constituent Accounting  
Entry. It doesn't make any of the Accounting Entries more unique  
(since they all by nature have the same date).

> Normalizing it out is nor necessarily an advantage.

I can see disadvantages of requiring multiple updating (changing the  
date in one Accounting Entry requires updating the Date for all other  
Accounting Entries that are part of the same Transaction), wasted  
storage etc. I can't see any advantages.

Or perhaps you actually also meant to imply that Date should be stored  
once for the Transaction (not for each Entry within it)?

> It has been my observation that accounting data is generally stored  
> in poorly thought-out data structures which then require layer upon  
> layer of logic to overcome the deficiencies.

Thanks then for your insight into the matter :-) I want to get it  
right from the outset. In my experience poor normalization  
necessitates layers of fixes down the road.

Here is the basic schema from before. I welcome any direct suggestions  
about the schema:

>> create table "Accounting Entry"
>> (
>>ID
>>  , "Transaction ID"  --> "Accounting Transaction".ID
>>  , "Account ID"  --> Account.ID
>>  , Amount
>>  , Operator  -- plus or minus
>> )
>> ;
>> create table "Accounting Transaction"
>> (
>>ID
>>  , Date
>>  , Description
>> )
>> ;
>> create table Account
>> (
>>ID
>>  , Description
>> )
>> ;

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Stanton]

"Double Entry" book keeping is actually a misnomer.  A transaction is 
very likely to have more than two entries.  The "transaction ID" I 
mentioned is a locally generated reference ID to tag the particular 
complete transaction, which might include postings to a bank account, 
sales, cost of sales, inventory etc.  It is not conventionally used, but 
confers great advantages when it is implemented.  It lets you locate the 
balancing entries for any accounting transaction.  In other words given 
any element of an accounting transaction you can complete the T Account.

Dating each accounting transaction aids in creating a unique ID. 
Normalizing it out is nor necessarily an advantage.

It has been my observation that accounting data is generally stored in 
poorly thought-out data structures which then require layer upon layer 
of logic to overcome the deficiencies.



BareFeet wrote:
> Hi John,
> 
> Thanks for the input.
> 
>> In general you need for one transaction -
>> General Ledger account
>> Date
>> Reference ID
>> Cross Reference
>> Amount
>> Optional narrative
>>
>> The Chart of Accounts defines full set of accounts and the details of
>> each account.
> 
> OK, that seems very similar to the schema I offered before (appended  
> below), with some different names:
> 
> General Ledger account  = Account ID
> Date= Date
> Reference ID
> Cross Reference = Transaction ID
> Amount  = Amount
> Optional narrative  = Description
> 
> I assume that your Reference ID is for references such as cheque  
> number, invoice number etc, but linked outside of this basic schema.
> 
> You seem to suggest storing the date for each entry within a  
> transaction. Would it not be better to store it once only for the  
> transaction as a whole, since each entry will share that same date?
> 
>> If each set of entries is an atomic transaction which balances  
>> debits and credits then the ledger is always balanced.
> 
> Yes, I had concluded the same, since it's kind of the definition of  
> double entry bookkeeping. I had wondered, though, since most  
> transactions involve two entries for the same amount but for two  
> different accounts, whether storing that same amount twice would not  
> only be redundant but actually de-normalized from an SQL point of  
> view. Is it preferable to have a schema where the amount is only  
> stored once in those circumstances?
> 
>> Having a separate index linking balancing transactions makes  
>> displaying
>> accounting activity much clearer.  You could call the cross  
>> reference a
>> "journal ID" or a similar name, since it has some functional  
>> similarity
>> to the traditional bookkeeper's general journal.
> 
> Your cross reference or "Journal ID" seems the same as the  
> "Transaction ID" I included in my earlier schema.
> 
>> Make sure that you insert each set of accounting transactions as one
>> Sqlite transaction which rolls back should it fail to successfully
>> complete, thus maintaining the balance of the ledger.
> 
> Good tip :-)
> 
>>> I found this:
>>> Modeling Business Rules: Data Driven Business Rules
>>> http://www.tdan.com/view-articles/5227
>>> which, seems similar to the route I was taking. If I'm interpreting  
>>> it
>>> correctly, the TDAN article suggests a relationship between  
>>> Accounting
>>> Entry and Accounting Transaction:
>>>
>>> create table "Accounting Entry"
>>> (
>>>   ID
>>> , "Transaction ID"  --> "Accounting Transaction".ID
>>> , "Account ID"  --> Account.ID
>>> , Amount
>>> , Operator  -- plus or minus
>>> )
>>> ;
>>> create table "Accounting Transaction"
>>> (
>>>   ID
>>> , Date
>>> , Description
>>> )
>>> ;
>>> create table Account
>>> (
>>>   ID
>>> , Description
>>> )
>>> ;
>>>
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John,

Thanks for the input.

> In general you need for one transaction -
> General Ledger account
> Date
> Reference ID
> Cross Reference
> Amount
> Optional narrative
>
> The Chart of Accounts defines full set of accounts and the details of
> each account.

OK, that seems very similar to the schema I offered before (appended  
below), with some different names:

General Ledger account  = Account ID
Date= Date
Reference ID
Cross Reference = Transaction ID
Amount  = Amount
Optional narrative  = Description

I assume that your Reference ID is for references such as cheque  
number, invoice number etc, but linked outside of this basic schema.

You seem to suggest storing the date for each entry within a  
transaction. Would it not be better to store it once only for the  
transaction as a whole, since each entry will share that same date?

> If each set of entries is an atomic transaction which balances  
> debits and credits then the ledger is always balanced.

Yes, I had concluded the same, since it's kind of the definition of  
double entry bookkeeping. I had wondered, though, since most  
transactions involve two entries for the same amount but for two  
different accounts, whether storing that same amount twice would not  
only be redundant but actually de-normalized from an SQL point of  
view. Is it preferable to have a schema where the amount is only  
stored once in those circumstances?

> Having a separate index linking balancing transactions makes  
> displaying
> accounting activity much clearer.  You could call the cross  
> reference a
> "journal ID" or a similar name, since it has some functional  
> similarity
> to the traditional bookkeeper's general journal.

Your cross reference or "Journal ID" seems the same as the  
"Transaction ID" I included in my earlier schema.

> Make sure that you insert each set of accounting transactions as one
> Sqlite transaction which rolls back should it fail to successfully
> complete, thus maintaining the balance of the ledger.

Good tip :-)

>> I found this:
>> Modeling Business Rules: Data Driven Business Rules
>> http://www.tdan.com/view-articles/5227
>> which, seems similar to the route I was taking. If I'm interpreting  
>> it
>> correctly, the TDAN article suggests a relationship between  
>> Accounting
>> Entry and Accounting Transaction:
>>
>> create table "Accounting Entry"
>> (
>>ID
>>  , "Transaction ID"  --> "Accounting Transaction".ID
>>  , "Account ID"  --> Account.ID
>>  , Amount
>>  , Operator  -- plus or minus
>> )
>> ;
>> create table "Accounting Transaction"
>> (
>>ID
>>  , Date
>>  , Description
>> )
>> ;
>> create table Account
>> (
>>ID
>>  , Description
>> )
>> ;
>>

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Stanton]

In general you need for one transaction -
General Ledger account
Date
Reference ID
Cross Reference
Amount
Optional narrative

The Chart of Accounts defines full set of accounts and the details of 
each account.

The G/L A/C indicates whether it is an asset or liability account (plus 
or minus) and how it is grouped in reports, the a/c, date amd reference 
ID are a unique ID and the "cross reference" can be a key on an index 
which ties together balancing entries in each "T Account".  If each set 
of entries is an atomic transaction which balances debits and credits 
then the ledger is always balanced.

Having a separate index linking balancing transactions makes displaying 
accounting activity much clearer.  You could call the cross reference a 
"journal ID" or a similar name, since it has some functional similarity 
to the traditional bookkeeper's general journal.

Make sure that you insert each set of accounting transactions as one 
Sqlite transaction which rolls back should it fail to successfully 
complete, thus maintaining the balance of the ledger.

BareFeet wrote:
> Hi Rich,
> 
>>> I'm trying to design a schema for double entry book-keeping. Is there
>>> already a schema available that provides this functionality?
>>   Well, if you're going to re-invent the wheel, take a look at the  
>> source
>> code for gnucash or, better yet, SQL-Ledger.
> 
> Thanks for your reply. I can't find the actual schema for either of  
> those packages. But what I have seen of them indicates that they cater  
> for a much broader solution than what I'm after.
> 
> I'm just looking for the general schema or data model for storing  
> double entry accounting transactions.
> 
> I found this:
> Modeling Business Rules: Data Driven Business Rules
> http://www.tdan.com/view-articles/5227
> which, seems similar to the route I was taking. If I'm interpreting it  
> correctly, the TDAN article suggests a relationship between Accounting  
> Entry and Accounting Transaction:
> 
> create table "Accounting Entry"
> (
> ID
>   , "Transaction ID"  --> "Accounting Transaction".ID
>   , "Account ID"  --> Account.ID
>   , Amount
>   , Operator  -- plus or minus
> )
> ;
> create table "Accounting Transaction"
> (
> ID
>   , Date
>   , Description
> )
> ;
> create table Account
> (
> ID
>   , Description
> )
> ;
> 
> Is tat the general model others have used, experienced, seen,  
> implemented?
> 
> Thanks,
> Tom
> BareFeet
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi Rich,

>> I'm trying to design a schema for double entry book-keeping. Is there
>> already a schema available that provides this functionality?
>
>   Well, if you're going to re-invent the wheel, take a look at the  
> source
> code for gnucash or, better yet, SQL-Ledger.

Thanks for your reply. I can't find the actual schema for either of  
those packages. But what I have seen of them indicates that they cater  
for a much broader solution than what I'm after.

I'm just looking for the general schema or data model for storing  
double entry accounting transactions.

I found this:
Modeling Business Rules: Data Driven Business Rules
http://www.tdan.com/view-articles/5227
which, seems similar to the route I was taking. If I'm interpreting it  
correctly, the TDAN article suggests a relationship between Accounting  
Entry and Accounting Transaction:

create table "Accounting Entry"
(
  ID
, "Transaction ID"  --> "Accounting Transaction".ID
, "Account ID"  --> Account.ID
, Amount
, Operator  -- plus or minus
)
;
create table "Accounting Transaction"
(
  ID
, Date
, Description
)
;
create table Account
(
  ID
, Description
)
;

Is tat the general model others have used, experienced, seen,  
implemented?

Thanks,
Tom
BareFeet

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users