The Problem
I am creating a financial system where I need to register a recurring movement, such as a light bill that repeats itself every month. In the system, this type of transaction is treated as a fixed expense / revenue.
My table has the following structure:
[tabela de movimentações]
- id // Código do registro (int, auto increment)
- title // Um título para a movimentação (var_char)
- description // Descrição da movimentação (text)
- type // Se é receita ou despesa (tinyint)
- repeat // Tipo de repetição (tinyint)
- is_recurring // Se é uma movimentação recorrente ou não (tinyint)
- start_date // Data de início (timestamp)
- value // valor da movimentação (decimal) só preenchido se for recorrente
The field repeat writes an integer that represents the repeat interval, being:
0 - Nunca
1 - Todos os dias
2 - Toda semana
3 - A cada duas semanas
4 - Todo mês
5 - A cada dois meses
6 - A cada três meses
7 - A cada seis meses
8 - Todo ano
If the movement is not recurring and has predefined portions, one or more, they are already saved in another table that has the following structure:
[tabela de parcelas]
- id // Código do registro (int, auto increment)
- value // valor da movimentação (decimal)
- date // Data da parcela (date)
- status // Status da parcela (tinyint) pago, pendente...
- trasanction_id // Referencia á primeira tabela para conseguir recuperar todas as
// parcelas envolvidas em uma movimentação
So I can recover all the parcels, but my problem is the recurring ones, which only go into the parcel table after they have been made.
How to set up a future spending forecast considering these recurring moves.
I can not leave by adding multiple records in the parcel table until a certain date, because if a report is generated for a period longer than the last record, the user would make wrong decisions without knowing that that fixed revenue / expense was not considered.
The solution can be a mix of PHP and MySQL as long as it does not get too heavy.
Example
I have a recipe of R $ 10,00 reais that I receive every week from 08/04/2014.
A of 04/08/2014 has already been removed and has already been recorded in the parcels table as paid. The future does not yet.
I need to list all revenue in the period from 08/01/2014 to 05/09/2014.
The above revenue should appear on the 04/08, 08/08, 08/08, 08/08 and 08/09 dates in this listing
Solutions found that did not meet
I found some solutions, which I quote below:
Possible solution 1
In this solution a table is used to store when the data should be repeated, using a mechanism similar to cron. It works, but the performance is horrible for multi-day reports.
The query used in the solution is based on the account on a specific day, so in order to set up a monthly report, I will perform 30 queries in the database, and for an annual report, 365 queries.
Possible solution 2
This solution uses a calendar table. A solution breaks branch, which can bring many future maintenance problems, such as lack of dates entered in the calendar table, volume of records stored unnecessarily, etc.