I thought I’d have some fun and design a database to mimic the structure of the budgeting app I currently use, Bluecoin. Obviously it will be simplified, and I’m not going to actually use it, but I thought I would enjoy the exercise. A bit like seeing how many pull-ups you can do, just for the sake of it.

One thing that has popped up a few times when using this app is the synchronising between devices. Since all of the data is local, it will save a local backup, check for new entries in Google Drive, and if there are no conflicts, upload the local backup. However, if there are entries on Google Drive that aren’t local, but local has more, for example, you need to decide which version to keep - you can’t “merge” the differences. My first thought in handling that a little better with this design is to include a LastModifiedDatetimeUTC and DeletedFlag for each table. That was, individual records can be checked to see who most recently updated it and you can keep that version, even if it’s been deleted.

CREATE DATABASE Bluecoin
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Bluecoin', FILENAME = N'C:\Databses\Bluecoin.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Bluecoin_log', FILENAME = N'C:\Databses\Bluecoin_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

USE Bluecoin
GO

DROP TABLE IF EXISTS dbo.TransactionLabels
DROP TABLE IF EXISTS dbo.Transactions
DROP TABLE IF EXISTS dbo.Reminders
DROP TABLE IF EXISTS dbo.Schedules
DROP TABLE IF EXISTS dbo.Accounts
DROP TABLE IF EXISTS dbo.AccountTypes
DROP TABLE IF EXISTS dbo.Categories
GO

/*This will store entries like "Bank Account", "Credit Card", "Shares", etc*/
CREATE TABLE dbo.AccountTypes (
				AccountTypeID			INT IDENTITY(1,1) NOT NULL,
				AccountTypeName			VARCHAR(200) NOT NULL,
				AccountTypeGroup		VARCHAR(20) NOT NULL, /*"Asset" or "Liability"*/
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_AccountTypeID PRIMARY KEY CLUSTERED ( AccountTypeID )
				)

/*This will store individual bank accounts, share trading accounts, credit cards, properties, your wallet, and even less
concrete accounts like "Money I owe"*/
CREATE TABLE dbo.Accounts (
				AccountID		INT IDENTITY(1,1) NOT NULL,
				AccountName		VARCHAR(200) NOT NULL,
				AccountTypeID	INT NOT NULL,
				OpeningDate		DATE NOT NULL,
				StartingBalance	DECIMAL(10,2) NOT NULL,
				DefaultCurrency	VARCHAR(3) NOT NULL,
				Notes			NVARCHAR(2000) NULL,
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_AccountID PRIMARY KEY CLUSTERED ( AccountID ),
				CONSTRAINT FK_Accounts_AccountTypes FOREIGN KEY ( AccountTypeID ) REFERENCES dbo.AccountTypes ( AccountTypeID )
				)

/*Groceries, Electricity, Books, etc*/
CREATE TABLE dbo.Categories (
				CategoryID		INT IDENTITY(1,1) NOT NULL,
				CategoryName	VARCHAR(200) NOT NULL,
				CategoryGroup	VARCHAR(200) NOT NULL,
				--BudgetID		INT NOT NULL, /*In the app, you can set a budget at a category level or a category group level. Not sure yet how I'd handle it*/
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_CategoryID PRIMARY KEY CLUSTERED ( CategoryID )
				)

CREATE TABLE dbo.Transactions (
				TransactionID			INT NOT NULL,
				TransactionSplitID		INT NOT NULL, /*If you have a single transaction that you want to split between two category, it will be stored as two rows with the same TransactionID but a different TransactionSplitID.
                I think that's a way I could handle transfers between accounts, too.*/
				TransactionDescription	VARCHAR(200) NOT NULL,
				TransactionType			VARCHAR(20) NOT NULL, /*Expense, Income, Transfer*/
				Currency				VARCHAR(3) NOT NULL,
				Amount					DECIMAL(10,2) NOT NULL,
				ExchangeRate			DECIMAL(10,6) NULL, /*I'm unsure how to handle other currencies just yet, but the app seems to record the exchange rate on the transaction record if it's non-default*/
				TransactionDate			DATE NOT NULL,
				TransactionTime			TIME(0) NOT NULL,
				AccountID				INT NOT NULL,
				CategoryID				INT NOT NULL,
				TransactionStatus		VARCHAR(10) NULL,
				Notes					NVARCHAR(2000) NULL,
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_TransactionID PRIMARY KEY NONCLUSTERED ( TransactionID, TransactionSplitID ),
				CONSTRAINT FK_Transactions_Accounts FOREIGN KEY ( AccountID ) REFERENCES dbo.Accounts ( AccountID ),
				CONSTRAINT FK_Transactions_Categories FOREIGN KEY ( CategoryID ) REFERENCES dbo.Categories ( CategoryID ),
				INDEX IDX_Transactions_TD_TT_CI CLUSTERED ( TransactionDate, TransactionTime, CategoryID ),
				)

CREATE NONCLUSTERED INDEX IDX_AccountID_Include ON dbo.Transactions ( AccountID ) INCLUDE ( Amount, Currency, DeletedFlag, ExchangeRate )
CREATE NONCLUSTERED INDEX IDX_CategoryID_Include ON dbo.Transactions ( CategoryID ) INCLUDE ( Amount, Currency, DeletedFlag, ExchangeRate )

/*The labels can be very generic, and you can have many for each transaction. You could have "Travel", or "Tax", for example*/
CREATE TABLE dbo.TransactionLabels (
				TransactionID		INT NOT NULL,
				TransactionSplitID	INT NOT NULL,
				TransactionLabel	VARCHAR(100) NOT NULL,
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_TransactionLabels PRIMARY KEY CLUSTERED ( TransactionID, TransactionLabel, TransactionSplitID ),
				CONSTRAINT FK_TransactionLabels_Transactions FOREIGN KEY ( TransactionID, TransactionSplitID ) REFERENCES dbo.Transactions ( TransactionID, TransactionSplitID )
				)

/*The schedules are used for recurring reminders. You can have them occur every 3 days, or every 2nd month on the 1st Saturday, etc*/
CREATE TABLE dbo.Schedules (
				ScheduleID				INT IDENTITY(1,1) NOT NULL,
				ScheduleType			VARCHAR(10) NOT NULL, /*Once, daily, Weekly, Monthly, Annually*/
				RepeatEvery				INT NULL,
				ScheduleStartDate		DATE NOT NULL,
				ScheduleStartTime		TIME(0) NOT NULL,
				EndTimes				INT NULL,
				EndDate					DATE NULL,
				AutomaticallyEnterFlag	BIT NOT NULL,
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_ScheduleID PRIMARY KEY CLUSTERED ( ScheduleID )
				)

/*This table is basically the same as the transactions table, but with a schedule. If you create a recurring reminder, each occurance will be inserted into this table up to 12 months into the future, which more easily allows you to edit the schedule from some future date*/
CREATE TABLE dbo.Reminders (
				ReminderID				INT NOT NULL,
				ReminderSplitID			INT NOT NULL,
				ReminderDescription		VARCHAR(200) NOT NULL,
				ReminderType			VARCHAR(20) NOT NULL, /*Expense, Income, Transfer*/
				Currency				VARCHAR(3) NOT NULL,
				Amount					DECIMAL(10,2) NOT NULL,
				ReminderDate			DATE NOT NULL,
				ReminderTime			TIME(0) NOT NULL,
				AccountID				INT NOT NULL,
				CategoryID				INT NOT NULL,
				ReminderStatus			VARCHAR(10) NULL,
				Notes					NVARCHAR(2000) NULL,
				ScheduleID				INT NOT NULL,
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_ReminderID PRIMARY KEY NONCLUSTERED ( ReminderID, ReminderSplitID ),
				CONSTRAINT FK_Reminders_Accounts FOREIGN KEY ( AccountID ) REFERENCES dbo.Accounts ( AccountID ),
				CONSTRAINT FK_Reminders_Categories FOREIGN KEY ( CategoryID ) REFERENCES dbo.Categories ( CategoryID ),
				INDEX IDX_Transactions_TD_TT_CI CLUSTERED ( ReminderDate, ReminderTime, CategoryID ),
				CONSTRAINT FK_Reminders_Schedules FOREIGN KEY ( ScheduleID ) REFERENCES dbo.Schedules ( ScheduleID )
				)

What I’d like to add next are some views on a few of the tables, mostly to display the text from the foreign key references, and do some calculations like “Current Balance” for the accounts. Then I’d like to design some stored procedures that could be used to create new accounts, create new transactions or reminders, or update any existing objects.

Other Bluecoin features I’m missing from my database:

  • Budgets
    • By Category
    • By Category group
  • Display net worth
  • Project net worth
  • Display running balance of accounts
  • Display current balance of account
  • Handling non-default currencies in the running totals, budgets, etc

I doubt I’ll end up writing code for all of these - I’m not trying to build an app at this point.

Here are some view I’ve written last minute:

CREATE OR ALTER VIEW dbo.AccountsDisplay
AS
SELECT		A.AccountName,
            AT.AccountTypeName,
            AT.AccountTypeGroup,
            A.OpeningDate,
            A.DefaultCurrency,
            A.StartingBalance,
			A.StartingBalance + ISNULL( T.TotalAmount, 0 ) AS CurrentBalance,
            A.Notes
FROM		dbo.Accounts AS A
				INNER JOIN	dbo.AccountTypes AS AT
					ON		AT.AccountTypeID = A.AccountTypeID
				LEFT JOIN	(
							SELECT		AccountID,
										SUM( Amount ) AS TotalAmount
							FROM		dbo.Transactions
							WHERE		DeletedFlag = 0
							GROUP BY	AccountID
							) AS T
					ON			T.AccountID = A.AccountID
WHERE		A.DeletedFlag = 0
GO

CREATE OR ALTER VIEW dbo.TransactionsDisplay
AS
SELECT		T.TransactionDescription,
            T.TransactionType,
            T.Currency,
            T.Amount,
            T.TransactionDate,
            T.TransactionTime,
			A.AccountName,
			C.CategoryName,
			C.CategoryGroup,
            T.TransactionStatus,
			A.StartingBalance + SUM( T.Amount ) OVER ( PARTITION BY T.AccountID ORDER BY T.TransactionDate, T.TransactionTime, T.TransactionID, T.TransactionSplitID ) AS AccountRunningBalance,
            T.Notes
FROM		dbo.Transactions AS T
				INNER JOIN	DBO.Accounts AS A
					ON			A.AccountID = T.AccountID
				INNER JOIN	DBO.Categories AS C
					ON			C.CategoryID = T.CategoryID
WHERE		T.DeletedFlag = 0