Reporting - Consistency of data when prices are changed

Question

I have the following database:

Requirements:

• the prices of the lunch boxes and the address of the customers can be amended as necessary.

• every week I need to generate a report from that database, which shall contain the information according to the time of the operation.

Problem:

If I modify the value of a lunch box, all orders made using reference to this lunch box will become useless, reporting something that is not correct, making the calculated total value of the already completed orders change (unacceptable). then I must make a choice:

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

  1. create other tables, just for the report, where each request saves all duplicated information so there is no total price inconsistency in the report.

  2. do not change the values, just mark the box as 'inactive' and create another with changed value, the same with the customer (removing the phone as a primary key).

  3. duplicate only the fields that change, in the requested item the price of the lunch box, and in the order the address and reference point of the customer.

  4. do not care about the items or address, just put in the order a Total Value field

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

  1. generate the report while the requests are performed. for example, each request add a line in an Excel file of the respective week.

  2. prevent changes from being made, unless it is immediately after having done the report of the week (which is saved in another file, for example Excel spreadsheet), and there is still no request performed.

any of these options is acceptable to my scenario, I just ask what would be the "most correct" (or if I thought about it in a totally wrong way and generating report is something else), since almost all break normalization, or why the database exists for conversation start.

Answer 1

We have no way of telling 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 is to impose an artificial restriction by default of the software. I don’t think you appreciate how bad this is.

The first one seems more appropriate, but you have to do it right. If you don’t know how to do it or you think it’s going to change a lot, a lot of work, then do it the way you want, but you can pay a price in the future.

1. The problem is not the report, it is the whole consistency of the system. The solution must solve the problem conceptually correctly, that is, the value of the lunch box is not unique, and has an expiration date.

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

3. I don’t like it, but it’s a simple solution that many adopt. Evaluate if the effort to do something more correct does not compensate, mainly because you have never done it and will have some difficulty. It may be an option. You won’t learn to do "more right," but you will deliver the service.

4. I think it’s unfeasible. It’s a ploy to get rid of the problem. And I don’t even think it solves anything.

I’m talking about it in When it is interesting to denormalize the database? and Modeling for shared address table.