[ACCEPTED]-Personal finance app database design-database-design

If I were to design a minimalistic accounting 19 application, I would probably do something 18 like

ledger
-------------
   key          INT(12) PRIMARY KEY
   account_id   INT(10)
   category_id  INT(10)
   trans_type   CHAR(3)
   amount       NUMERIC(10,2)

account
------------
   account_id   INT(10) PRIMARY KEY
   created      DATETIME
   name         VARCHAR(32)
   ...

category
------------
   category_id  INT(10)
   name         VARCHAR(32)
   ...

The column key would consist of a date 17 and a zero-padded numeric value (i.e. 201102230000) where 16 the last 4 digits would be the daily transaction 15 id. This would be useful to track the transactions 14 and return a range, etc. The daily transaction 13 id 0000 could be the account balance at the 12 beginning (or end) of the day, and the id 11 0001 and up are other transactions.

The column 10 trans_type would hold transaction codes, such as "DEB" (debit), "CRE" (credit), "TRA" (transfer) and 9 "BAL" (balance), etc.

With a setup like that, you 8 can perform any kind a query, from getting 7 all the "credit" transactions between any 6 given date, to only the account balance 5 at any given date, or date range.

Example: fetch 4 all credit and debit transactions between 3 2011-01-01 and 2011-02-23

SELECT ledger.*, account.name, category.name
  FROM ledger
  JOIN account
    ON ledger.account_id = account.account_id
  JOIN category
    ON ledger.category_id = category.category_id
 WHERE (ledger.trans_type = "CRE"
     OR ledger.trans_type = "DEB")
   AND ledger.key BETWEEN 201101010000 AND 201102239999
 ORDER BY ledger.key ASC

Example: fetch all transactions (except balances) between 2 2011-01-01 and 2011-02-23 for the account #1 (ex: Mortgage)

SELECT ledger.*, account.name, category.name
  FROM ledger
  JOIN account
    ON ledger.account_id = account.account_id
  JOIN category
    ON ledger.category_id = category.category_id
 WHERE ledger.trans_type <> "BAL"
   AND ledger.key BETWEEN 201101010000 AND 201102239999
   AND account.id = 1
 ORDER BY ledger.key ASC

So 1 there you go, flexibility and extensibility.

For a personal financial database today's relational 9 database systems are plenty fast enough 8 to calculate the balance of multiple accounts 7 dynamically. You don't need a column to 6 hold the current balance. Even Microsoft 5 Access is fast enough. I know this because 4 I built and use a personal financial database 3 in Access. It might even be what you were 2 originally looking for. You can read about 1 it and download it at http://maiaco.com/software/ledger/index.php

I am actually working on just this website 6 idea right now and the way I've setup my 5 database is:

TABLE account
    id
    account_name
    current_balance

TABLE transaction
    id
    account_id
    payee
    date
    amount
    category

And whenever a new transaction 4 is added I update the account's current 3 balance.

FYI, I hope to launch my site within 2 a month and if you're interested in using 1 person's site, just check out my profile.

I would think a single table read would 3 be better and allow for more flexibility 2 in the future. You could eventually track 1 averages for balance, credits and debits.

Don't store calculated values in tables 3 unless you need to for performance reasons. I 2 would use a View to exposes the calculated 1 values instead.