I have a monthly expense for 36 months or I have an indefinite quarterly revenue. What would be the best way to store this information with a view to minimizing the size of the database and making it easier to search for records in a given time period?

One record per month. It does not seem to be the most reasonable, but I will say why it is.

First, historical. Considering also expenses, the complexity of your application would go up a lot if you had to analyze recurring expenses and expenses (I'm assuming they are two different entities). Here or you would have to prohibit the change after the posting (which is bad for the application and would cause the customer uncanny), or audit each change and recompose the entire balance based on these changes, which would make your work a hell. / p>

Second, consistency. There is the aggravating of the user changing, for example, the day that this expense will occur after, let's say, 18 months. If it were just a record, changing the date would cause the system to run into the problem of changing the date of a retroactive expense. This I have not even talked about values, nor months closing, which are common things in revenue and expenditure systems. Keeping these records unchanged after closing is critical to the credibility of the system.

My first idea is a table that stores the occurrence start date, the type of record (Revenue or Expense), repetitions (how many times this occurrence will repeat), frequency (annual, daily, semiannual, ...) and value.

This works fine to leave logged to the system when it should replicate an expense record, but not as definite record of the actual expense.