Hi all,
I'm trying to design a schema for double entry book-keeping. Is there
already a schema available that provides this functionality?
As I see it, I need to start with something like the schema below. Any
other approaches?
create table Accounts
(
Code text unique collate nocase
, Name text collate nocase
)
;
create table "Moves" -- Move of money in one direction as
part of an
event
(
ID integer primary key
, Event integer --> Events.ID
, Account text --> Accounts.Code
, Amount integer -- money in cents, +ve or -ve
)
;
create table Descriptions -- For each Move, Statement text from
the
bank or manual description
(
ID integer primary key --> Moves.ID
, Description text -- text shown on bank statement or
manually entered
)
;
create table "Events" -- Event where money moves into and
from 2 or
more accounts
(
ID integer primary key
, Date date -- julianday when event occured
)
;
create view "Moves Entry"
as
select
Moves.ID as rowid
, Moves.ID as ID
, Moves.Event as Event
, date(Events.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
, Description
from Moves
left join Events on Moves.Event = Events.ID
left join Descriptions on Moves.ID = Descriptions.ID
left join Accounts on Moves.Account = Accounts.Code
;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
