If I am managing a club which has expenses (e.g. coffee, building repairs, electricity, cake, etc). Every time the club takes on an expense, then a record is added to the 'All Actual Expenses' table:
Code:
______________________ _______________________
|All Possible Expenses | |All Actual Expenses |
----------------------- -----------------------
| ID | | ID |
| Name of expense | | AllPossibleExpensesID |
----------------------- | Date Occured |
| Price |
-------------------------
Expenses can be arranged into funds (like 'Expenses of year 2017', 'Food Expenses', 'Expenses of business meeting held to discuss premises' etc.).
So the tables for funds is:
Code:
___________ _________________________________________
| Funds | | Funds LINK AllActualExpenses |
------------| |-----------------------------------------|
| Id | | FundID |
| Fund Name | | AllActualExpensesID that's in this fund |
------------ -----------------------------------------
As you can see, funds can 'overlap', so an expense can be part of a few funds.
When money is paid in to the club's coffers, the money can be allocated only to certain funds, like so:
Code:
_________________ _____________________________________
|Payments | | Payments LINK Funds |
-----------------| |------------------------------------|
| ID | | PaymentID |
| Date | | A FundID that this is allocated to |
| Amount Paid In | -------------------------------------
| Member ID |
-----------------|
This LOOKS good, but there's a problem I can't for the life of me work out what to do:
How do I show how much money each fund has in it? Remember, some money might be allocated to Funds ONE and TWO, while other money might be allocated to Funds TWO and THREE...so in reality, is there a good way to show how much money is in Funds ONE, how much money is in Fund TWO, and how much money is in Fund THREE?
The problem is, if the program simply shows how much money is in each fund, it will seem to the user that the club has far more money than it really has!
By extension, the following issues also crop up:
- What if some money isn't given to any funds...how will the program allocate it? How will the program show this money?
- If someone wants to give money towards certain expenses which aren't grouped in one fund alone, should I create a 'custom fund' for them? If I put the money straight towards the expenses, I will violate database theory (I think) because some money will be allocated direct to expenses and some money will be allocated to Funds!
I dunno, maybe I'm overlooking an obvious solution. Has this model been tackled before? Thanks in advance for any ideas.