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 able to give you some ideas that may (or not) help you.
The intent is not to frustrate you, but to give you information to tailor
your system to meet your needs.
BTW, my accounting systems experience has so far included McCormack & Dodge,
MSA, Software International (aka CA Universe), Lawson, SmartStream, Oracle,
PeopleSoft, Coda, Clarus, NetSuite, QuickBooks, Platinum, Solomon, GNUCash,
some homegrown systems, and a sampling of others on various OS's and
databases. And, I have worked (at various times) with systems for the USA,
Canada, Mexico, UK, France, Netherlands, and some exposure to Australian
rules (which include recording depreciation to 5 decimal places).
Alan B. Cohen, CPA
AIM/YM/GMT/MSMess/Skype: alanbcohen
Email: alanbco...@gmail.com ICQ: 49972123
Phone:+1 443/451-5369 Efax: +1 413/653-6474
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
;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
