Project experience Technical Summary 3: Basic System Business Data Maintenance

During system development, data interaction with other systems is encountered, including data download and Data Write-back. (The latter is not described in this article ). Data download means that the system needs to download basic business data from databases of other systems. After the data source is updated, the basic business data of the system also needs to be updated (Real-Time is not required ), the data source has nearly 1 million basic business data records for several tables.

For ease of description, we will only discuss one table. The data source table is a table, and the table downloaded to the system is B.

Ideas:
The process of downloading data from Table A to table B can be divided into two steps:
1. First download
The first download should be to insert all the corresponding data in Table A to table B (completed during system initialization)
This process is easy to handle, and only one SQL statement can be used.
2. Second (and later) download
There are two considerations: new data and original data update in table.
To determine which data needs to be processed, you can judge based on the last update time recorded in Table A (as the data source system generally has the last update time field of the record, which is marked as lastupdatetime below:
That is, if the value of lastupdatetime in each record in Table A is greater than the value of lastupdatetime in Table B, it is regarded as the data to be processed:
A. When such records do not exist in the system, the new business data is added.
B. Data to be updated for the system when such records exist in the system

Of course, if the value of lastupdatetime in Table A is not greater than the value of lastupdatetime in Table B, no processing is required.

Therefore, downloading data can be divided into two steps:
Step 1 download the new record to the System
Step 2: update the data to be updated
It should be noted that the lastupdatedate must be recorded or updated in the system, whether it is data insertion or data update. Otherwise, the lastupdatetime value of the record in Table A cannot be compared in the future.
Updating LastUpdateTime in Table B can update all LastUpdateTime of all records. However, it takes a certain amount of time to update a large amount of data. Therefore, you can create a separate table C and use a record to save the last update time of Table B. When updating Table C, you only need to update one record to Improve the availability.

Based on the above analysis, we have adopted the following solutions:
1. Create tables in the system and tables with similar structure (only the required data) in the data source
2. Create a table C to record the last update record date of each business data table in the system. The table structure is as follows:
Create Table C
(
TableName, varchar (50 ),
LastUpdateDate Date
)
3. When data is inserted or updated, LastUpdateTime of the corresponding record in Table C is updated each time based on the processed data.

Note that:
1. When updating LastUpdateTime for each table, the time of the table cannot use the system time, but the system time of the data source.
2. When the basic table is small (the amount of business data is small), you can delete the data in the target table and re-download the data each time you download it.