I have a monthly expense for 36 months or I have an indefinite quarterly income. What would be the best way to store this information, thinking about minimizing the size of the bank and making it easier to search for records in a certain period of time?
One record per month. It seems not to be the most reasonable, but I’ll tell you why it is.
First of all, historical. Also considering expenses, the complexity of your application would go up a lot if you had to analyze recurring expenses and expenses (I’m guessing they’re two different entities). Here or you would have to prohibit the change after the launch (which is bad for the application and would cause strangeness to the customer), or audit each change and recompose the whole balance based on these changes, which would make your work a living.
Secondly, there is consistency. There is the aggravation of the user changing, for example, the day that this expense will occur after, let’s say, 18 months. If it was just a record, changing the date would cause the system to incur the problem of changing the date of a retroactive expense. This I did not speak of values, nor of closing of months, which are common things in systems of income and expenses. Keeping these records unchanged after closure is critical to the credibility of the system.
My first idea is a table that stores the date of occurrence, the type of record (Revenue or Expense), repetitions (how many times this occurrence will be repeated), frequency (annual, daily, half-yearly,...) and value.
This works well to record the system when it should replicate an expense record, but not as a definitive record of the actual expense.
tried to draw a diagram (can be class/entity) of your idea?
– Samuel Diogo