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