Common Database Design (3) -- Record and change as many historical data problems common database design (2) -- change with a single record of historical data problems

Change to a single record of historical data: Common Database Design (2) -- change to a single record of historical data problems

1. Overview

There is a type of data stored in the historical customer operation data, such as the bidding documents, the refining scheme list, the price change of the product price, and the change list. Such historical data includes a mainstream water meter that controls the flow version and a list table corresponding to a specific version.

 

2. Requirements for multiple record changes and no future history records are stored in a single table

Business: when making a payment plan, you need to save the historical version data of the Plan and record the materials for which each plan pays.

Table Structure

Note: Like a single record, version control is included in the payment schedule. For details about how a subject implements version control, see change a ticket

3. Requirements for changing multiple records and storing future records are not required, and stored in multiple tables

Business: The business is the same as the business in business 2. However, when there are many payment lists and each payment plan has many versions, at this time, the amount of data in the payment plan list will be large. During normal access, we only need to know what the current available list is and do not pay attention to historical versions. When the amount of data in the List table reaches a certain level, the query efficiency will be seriously affected.

Structure:

The payment plan and payment plan list table are the same as above, and then a payment plan list change table is added.

4. Need to record changes and store future records

Business: When managing a supplier, there will be a demand for changing the product price, and multiple product prices will change together, and the price will change in the next month can be notified in advance. In this case, a streaming water meter is required to control the price change of multiple commodities, and this flow must support the function of storing historical data in the future.

Table Structure:

Note: add the effective date to the configuration table. Here, the change flow is the suppliergoodspricevar table, and the change list is the suppliergoodspricevardetails table. The available list is suppliergoods, indicating that the price Effective Date Field is added to the Change List where historical data is stored.