RocReadaR : Financial (Billing) Overview

All financials related to publisher accounts are run through two tables (See Database Schema Diagram):

• FinancialTransaction
• FinancialTransactionType

Each Financial Transaction has a foreign key to the Financial Transaction Type.

The type has a description and a boolean for "DirectionIsIncoming". Imagine each publisher has an account balance, like a bank account. When a Financial Transaction is created, "DirectionIsIncoming" = true, it means the transaction is moving in to the user's account. If "DirectionIsIncoming" = false, it means the transaction is moving out of the user's account. There are no negative amounts in transactions. Each amount should be positive. If you are removing money, it should be an out transaction!

The Financial Transaction Types that get added to the table should include the various types of transactions, not just "in" and "out". For example, if enough users scan pages of a publication and the publisher is charged, a Financial Transaction should be added of Type "Publication Scanned Bill" and direction out, because the publisher was charged or money was removed from the "account". If a publisher pays money to the sponsors to pay for the service, then a Financial Transaction should be added of Type "Publisher Service Payment" and direction in, because the publisher added money to the "account".

Some FinancialTransactionTypes might include:

• Publisher Service Payment
• Publication Scanned Bill
• Support Adjustment In
• Support Adjustment Out
• Monthly Service Fee

To get the current balance of a publisher, all you have to do is sum the transactions in and out of the account. Here is some quick pseudocode:

function GetPublisherBalance(publisherID) {
	var balance = 0;
	
	// start by getting all the transactions for the publisher
	var financialTransactions = getAllFinancialTransactionsForPublisher(publisherID);
	
	// loop through all the transactions
	for (var i in financialTransactions) {
		var financialTransaction = financialTransactions[i];
 
		// If the direction is IN, add the amount. Otherwise, subtract it
		if (financialTransaction.FinancialTransactionType.DirectionIsIncoming) {
			balance += financialTransaction.Amount;
		} else {
			balance -= financialTransaction.Amount;
		}
	}
 
	return balance;
}

Due to the small amount of expected transactions, this overhead of not saving the balance in one place is lessened.

One issue might arise in loading the direction of each financial transaction type. Below is an example of how to use a local cache. This example is only useful if loading FinancialTransactionType records must be done separately from the FinancialTransactionType.

function GetPublisherBalance(publisherID) {
	var balance = 0;
	
	var financialTransactions = getAllFinancialTransactionsForPublisher(publisherID);
 
	// Make an object to hold the cache for this call
	var financialTransactionDirs = { };
	for (var i in financialTransactions) {
		var financialTransaction = financialTransactions[i];
 
		// See if we already loaded this transaction type. 
		var financialTransactionType = financialTransactionDirs["TypeID_" + financialTransaction.FinancialTransactionTypeID];
		if (financialTransactionType == null) { // one wasn't found, so load it
			// load the transaction type
			financialTransactionType = getFinancialTransactionTypeFromFinancialTransactionTypeID(financialTransaction.FinancialTransactionTypeID);
 
			// store it in the cache
			financialTransactionDirs["TypeID_" + financialTransaction.FinancialTransactionTypeID] = financialTransactionType;
		}
 
		// If the direction is IN, add the amount. Otherwise, subtract it
		if (financialTransaction.FinancialTransactionType.DirectionIsIncoming) {
			balance += financialTransaction.Amount;
		} else {
			balance -= financialTransaction.Amount;
		}
	}
 
	return balance;
}

Concurrency is automatically maintained by only adding new financial transaction rows. Because we "don't care" if a balance goes into the negative, more an more financial transactions can be added simultaneously without error. Introducing a table or column to represent an account balance could bring concurrency issues, where updates are "lost".

A good example of this can be found here: http://stackoverflow.com/a/880815/5141403 - we solve this by not using updates but instead adding new records.