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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
