Idea

While on a trip to Jutland, I had a splendid idea: a system to track loans—who I loaned money to and whether I ever got it back. It had to work on mobile devices and as a web app, so I built it as a small webpage.

I used an MSSQL database (hosted by addnet.dk) and wrote the app in C#.

Database

I built a single main table, Finances, for all loans and edits. Each finance can have a parent—NULL indicates a root loan. This allows loan tracking and later edits. A root loan of +10 DKK, followed by a -10 DKK update, resolves to 0 (paid).

Loans can be updated multiple times in either direction. The Finances table is tied to two other tables:

  • Accounts: multiple accounts for organizing finances
  • Contacts: people who borrow or lend

Every finance is tied to one contact and one account.

To make loan status checks faster, I created a column that stores the current value of a root loan (sum of all children). A scalar function in MSSQL 2005 keeps it up to date:

CREATE FUNCTION [dbo].[GetFinancesNewValueByID] (@fid int)
RETURNS decimal(20, 2)
AS
BEGIN
    DECLARE @result decimal(20, 2)
    SELECT @result = SUM(fvalue) FROM dbo.finances WHERE (fid = @fid) OR (faffect = @fid)
    RETURN @result
END

Similar functions exist for accounts and contacts. That way, I always fetch pre-calculated results instead of computing on the fly.

Site

The frontend is minimal—black and white, except for loan values:

  • Red: Negative
  • Green: Positive
  • Black: Zero or neutral

Once a user account is set up, you can create contacts and accounts. These are essential for assigning and tracking loans.

I also implemented a way to move loans: combining smaller, independent loans into one. This was handy for situations like shopping for parents, then settling up at the end of the month.

Result

The final site is live at mobifinance.mbwarez.dk (link may be broken). It’s free for anyone to use. Although emails are collected, they’re not used (originally meant for signup/forgot password flows).

I’ve used the site daily since around June 4th, 2009. It’s stable and meets my needs. Most bugs were ironed out early on.