Reports - Consistency of data when prices are changed

Navigation
0

I have the following database:

Requirements:

  • The values of the kettles and the customers' addresses can be changed as needed.

  • Each week I need to generate a report for this database, which should contain the information according to the moment of the operation.

Problem:

If I modify the value of a kettle, all requests made using this kettle will become useless, reporting something that is not correct, making the total calculated value of the orders already made change (unacceptable). so I should make a choice:

  • allowing the report to be generated in the interval you want:

  • Create other tables, just for the report, where each request saves all duplicate information so there is no total price inconsistency in the report.

  • Do not modify the values, just mark the kettle as 'inactive' and create another one with changed value, the same with the client (removing the phone as the primary key).

  • Duplicate only the fields that change, in the requested item the value of the kettle, and in the request the address and reference point of the client.

  • Do not care about items or address, just place a Total Value field in the order

  • Allowing the report to be generated only from week to week:

  • Generate the report while the requests are made. for example, with each request appending a row to an Excel file for the respective week.

  • Prevent changes from being made, unless it is immediately after you have done the week's report (which is saved in another file, eg Excel worksheet), and no order has yet been made. p>

Any of these options is acceptable for my scenario, I just ask what would be the "most correct" (or if I thought about it totally wrong and generate report is something else), since almost all break standardization, or reason for the database to start.

    
asked by anonymous 29.09.2016 / 15:16

1 answer

1

We can not say which one is right for you. We can evaluate the options abstractly.

The second option, blocking operations, is completely meaningless. It is an alternative, but it is imposing an artificial restriction by default of the software. I do not think you rated it bad.

The first one seems to be more appropriate, but it needs to be done right. If you do not know how to do it or you think it will change a lot, give work, then do as you see fit, but you can pay a price in the future.

  • The problem is not the report, it's all system consistency. The solution should solve the problem in a conceptually correct way, that is, the value of the kettle is not unique, and has a shelf life.

  • The solution is more or less this. This is what we call temporal or historical data. Each change generates a new kettle considered as the previous ones stay there for historical reasons. Orders already made will always refer to the kettle and customer address actually used at the time of the operation. New orders will consider only the current kettle / client (which will no longer be current later). That is, the data is immutable .

  • I do not like this, but it's a simple solution that many adopt. Assess whether the effort to do something more right does not pay, mainly because you never did and will have some difficulty. It may be an option. You will not learn to do "more right", but will deliver the service.

  • I find it impractical. It's a gambiarra to get rid of the problem. And do not even think it solves anything.

    • I mention this in When is it interesting to denormalize the database? and Shared address table template .

        
    29.09.2016 / 15:32
    Unable to connect: No connection could be made because the target machine actively refused them SASS Identation 2 spaces?