Records with recurring dates filtered for a period

Asked

Viewed 1,971 times

8

The Problem

I am creating a financial system where I need to register a recurring movement, for example, a light bill that repeats every month. In the system, this type of movement 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 records an integer representing the repeat interval, they 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 non-recurring and has predefined plots, either one or several, 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 split moves, but my problem is the recurring ones, which only enter the plot table after they are made.

How to build a forecast of future expenses considering these recurrent movements.

I cannot add multiple entries to the installment 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 income/expense was not considered.

The solution can be a mix of PHP and Mysql as long as it doesn’t get too heavy.

Example

I have a revenue of R $ 10,00 real that I receive every week from 04/08/2014.

The day 04/08/2014 has been paid and has been registered in the table of installments as paid. The future not yet.

I need to list all recipes in the period from 01/08/2014 to 05/09/2014.

The above revenue should appear on the dates 04/08, 11/08, 18/08, 25/08 and 01/09 in this listing

Solutions found that did not meet

I have found some solutions, which I quote below:

Possible solution 1

In this solution a table is used to store when the data must be repeated, using a cron-like mechanism. It works, but the performance is horrible for reports cases of several days.

The query used in the solution is based on the account on a specific day, so, to assemble a monthly report, I will hold 30 consultations at the bank, and for an annual report, 365 queries.

Possible solution 2

This solution uses a calendar table. A branch break solution, which can bring many future maintenance problems, such as the lack of dates registered in the calendar table, volume of records stored without need, etc.

php mysql database date performance

  • I believe it would be better to generate multiple records and link these records to a recurring account, then at the time of generating you ask the user how much ends and what recurrence, then you generate all installments until the end date. Otherwise everything will get complicated, think of the reports you will have to generate

  • This type of movement has no end date, and it is precisely because of the reports that I need it. The registry of movement as revenue/fixed expenditure is used to make a forecast. If I always put a final date, the person can forget and make a serious management mistake by thinking that in the forecast that drive was being considered.

  • 3

    I see several problems in the desired exit. One of them, for example: if you change the value, you cancel the past release, lose all history and make new event? Intermediate tables in these applications are not merely facilitators, but ways to maintain consistency. One of the simplest solutions is a table with a "rolling" event, which updated each release leaving traces in the past (at the right time, the event drops a clone in the history, and is scheduled for the future date). The rolling event.

  • I am already saving what actually occurred in another table, this is not the problem, I keep the history. The problem is to recover the future data. For example, I have a quarterly account, which started on 07/10/2014, if I request a report for 08 month this account does not appear, but in a report for 10 month will appear. If I request a report from 1/07/2014 to 30/11/2014 this account has to appear twice

  • In this case I cannot clone the data after something happens, because I need to assemble future predictions, that is, I need to perform calculations that consider the recurrent record.

  • @marcusagm, I think I completely misunderstood your doubt... I removed my answer to not pollute.

  • All right @papa-charlie, this question is very difficult to come to a conclusion

Show 2 more comments

3 answers

3


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

With this, you can keep the tables separate 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 model. As it is, you cannot act, for example, 5 weeks or 10 months.

1

A possible solution would be a procedure returning cursors or even views assembling these virtual tables, depending 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.

  • The reasons for not wanting to save future data are: 1) A very high number of records generated on the system. 2) Processing time for updating all future records if there is any change. 3) Limit the system to work up to a maximum of years, because to register future data need to have a final date. 4) Possibility of decision error, the user generate a report whose drive was to be considered but it was not because future drives were registered until an earlier date, the user is not aware of this.

  • Reason 4 I can solve with cron, to always check and register the missing ones, but for that I would have to really limit my system to generate report for a maximum period. This prevents long-term projections by the system.

  • On the reuse of data, they are constantly accessed, whether to generate overview charts, alerts, simple or complete reports.

  • 1

    From what I understood would be data that will be carried out , information of the predicted type x carried out solves the question of the separation of the data. Sql is good to get recorded data, "missing" data can be mounted via artificials but in general are not good solutions.

0

If you have the report interval in number of days, why not use a 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 debit balance would be 60/15*value_tranche.

Since you already have a Tinyint code that represents the type of recurrence, all you have to do is associate it with an interval size. Evidently the dates that already passed would be outside the range.

Browser other questions tagged php mysql database date performance

You are not signed in. Login or sign up in order to post.