Records with recurring dates filtered by a period


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.


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.

asked by anonymous 28.08.2014 / 07:33

3 answers


You can generate the new records as they are needed, using a lazy strategy. So, if the user indicates that he wants to know of the expenses until May next year, for example, you generate. However, there is one annoying problem of consistency that is the recurring expenses amount being readjusted (eg my condo increased).

With this, you can keep separate tables and always display the already confirmed expenses separate from the estimates for recurring expenses.

I also suggest that you use a pair (number, unit) instead of your current template. As it stands, you can not represent, for example, 5 weeks or 10 months.

30.08.2014 / 03:40

A possible solution would be a procedure returning cursors or even views by mounting these virtual tables, it would depend on the reuse of this information. But I do not see many inconveniences in recording these future events, they would be modified when they occur.

29.08.2014 / 01:56

If you have the interval of the report in number of days, why not use simple 3 rule? For example, if the recurrence is every 15 days and the period is 60 days from today, you will have 4 installments. The outstanding balance would be 60/15 * share_value.

Since you already have a Tinyint code that represents the type of recurrence, you would simply associate it with an interval size. Of course the dates that have passed are out of range.

05.09.2014 / 04:55