I need to create a database of transactions to track loans between different entities, eg. Business A loans $10,000 to Business B.. and Business A also loans $5,000 to Business C, when Business B repays part of the loan to Business A, we want to be able to calculate interest on the funds borrowed for the number of days borrowed and add that the funds repaid. There are going to flows of funds between businesses constantly so will also want to create a report that shows the transactions for each separate loan and a list of the balances owed between the businesses.. It sounds simple but trying to design it is getting confusing
As a starting point I was trying to do the transaction with two lookup fields accessing a list of Entity Names, but realised I wouldnt be able to show the transaction amounts for each part to the transaction.. Should I have one table line for Business loaning the money and a separate transaction for Business B borrowing the money..
Interested to get feedback.. or suggestion as to where someone else may have already tackled this problem.