Database Model Design & amp; mdash; history and Version Design

In enterprise database design, we often encounter a requirement that we want to keep the data before the operation to see what data is before the operation and what data is after the operation. To meet this requirement, we can retain historical data or use versions. To retain historical data, the following solutions are provided during Version Design: 1. Use the version

In enterprise database design, we often encounter a requirement that we want to keep the data before the operation to see what data is before the operation and what data is after the operation. To meet this requirement, we can retain historical data or use versions. To retain historical data, the following solutions are provided during Version Design: 1. Use the version

In enterprise database design, we often encounter a requirement that we want to keep the data before the operation to see what data is before the operation and what data is after the operation. To meet this requirement, we can retain historical data or use versions.

To retain historical data, the following solutions are available during Version Design:

 

I. Use the version number

The version number is a common version design scheme. It adds a version number field to the table for historical data retention. This field can be of the DateTime or int type, during each data operation, a new version is created, and the version only increases or decreases. Therefore, you only need to get the largest version number to obtain the latest business data.

In addition to retaining historical data, the version number also prevents concurrent editing. For example, we have an object A with the current version of 1. The two users simultaneously open the editing page of the object and make data changes. First, user A submits the changes. At this time, the system queries the ID and version of the object and finds that the latest version of the data to be modified is 1. Therefore, the modification is successful and the new version 2 of object A is saved. At this time, user B also submitted modifications. The system queries the Object ID and Version 1 and finds that the latest data version to be modified is 2, which does not meet the requirements. Therefore, user B's modification is rejected. User B only refreshes the interface, obtains the latest version 2, and then modifies it.

ID	Ticket No.	Amount	Version Number
1	EXP123	100	1

When the version number is used, modify the amount of the document and then create version 2:

ID	Ticket No.	Amount	Version Number
1	EXP123	100	1
2	EXP123	120	2

Ii. Effective use and expiration time

The second method to save historical data is to use the validity period to indicate a version. Add the "effective time" and "expiration time" fields to the table for recording historical data. The two fields cannot be blank. The effective time of the newly created data is the time when the data is created, and the expiration time is 9999-12-31. Now we have modified this data, so we only need to set the current time to the expiration time of the previous version, and create a new data, the effective time is the current time, the expiration time is 9999-12-31.

ID	Ticket No.	Amount	Effective Time	Expiration time
1	EXP123	100	15:30:00	9999/12/31 23:59:59

For example, the preceding document was created on January 1, 100. Later, the document was modified at 15:00:00 on January 9, 120, and the amount was changed from January 1, to. The new data created when the document was saved is as follows:

ID	Ticket No.	Amount	Effective Time	Expiration time
1	EXP123	100	15:30:00	15:00:00
2	EXP123	120	15:00:00	9999/12/31 23:59:59

After the validity period and expiration time are used, we can query the values of data in the database at any time point. You only need to input the time to be queried, and then set the validity period and expiration time of.

The first two solutions require a business primary key to identify a specific business data. What if the entity we want to record does not have a clear business primary key such as "ticket number" and "Order Number? We can use the database primary key when creating data as the business primary key.

Employee ID	Name	Birthday	Business ID	Version Number
1	Zhang San	1984/12/29	1	1

For example, we have an employee table that records the basic information of the employee. when creating the employee data of Michael Jacob, the ID of the employee in the database is 1, you can set the Business ID to 1. Next, change the attributes of John and record the version. A new version will be created, and the primary key "employee ID" will change, however, the business primary key "Business ID" is always 1 and will not change.

Employee ID	Name	Birthday	Business ID	Version Number
1	Zhang San	1984/12/29	1	1
2	Zhang San	1985/1/9	1	2

Although the previous two schemes can well record historical data, every time the data is modified, the new version is generated and saved, so the IDs of each version are new, therefore, there must be a business primary key to identify an entity. The two examples here "single number" are their business primary keys. The changes in the primary key make all associated objects change to form a chain effect, so that each associated object also generates a new version. For example, we have an order system with an order table and order list. Now we need to record the previous version of the order modification, so we have increased the effective time and effectiveness time, and used the order number as the business primary key. There is now an order A, with 100 details below. If you want to modify the order, modify the attributes of A specific detail, resulting in changes to the entire order, then we need to create a new order data line. Because the primary key changes, order details need to be changed, so we need to create a new version for all the 100 details. In the order details of the new version, "Order ID" points to the ID of the new version of order data.

The problem caused by such a design is that the order list will expand rapidly. If an order contains 1000 details, we only modify the attributes of the order and do not modify the Order details, this will also cause the 1000 details to be copied and then saved. What should we do? You can use the following methods:

1. Create a version field for Order details to refine the version granularity to order details, rather than orders. There is no database-level foreign key relationship between the order and the Order details, and only the business-level foreign key relationship exists. That is to say, in addition to the effective time and expiration time in the order list, the "Order Number" field must be added for the table name of the order.

After this modification, if the order object is modified and the Order details are not modified (for example, the recipient information is changed), you only need to generate a new row of data in the order table, order details are not copied to generate new data. If we change the order details (unit price and quantity), we only need to change the order details, you do not need to change all the details of the entire order.

After using this design, to query the order and its details, you need to filter the effective expiration time of the two tables, and obtain the details through the order number, instead of the Order ID.

When you refine the versioning granularity to order details, the logic of the background program is more complex. The user operates on the order object on the interface. The system will upload the modified order object to the background. The background program needs to compare each order item. If the order item is found to have been modified, the method for generating the Order details of the new version is called.

2. Use a separate historical table

This is another method for implementing version history:

3. Use a separate historical table

Using a history table is actually creating a table with the same Schema (of course, you can also add more fields to record additional historical version information). This table only retains data of previous versions. This is a bit like an archive logic. We think that all historical versions should be infrequently accessed. All of them can be thrown into a separate table. For existing versions that take effect, they are still stored in the original table, if you want to query historical versions, query them from the history table.

Using a separate history table has the following benefits:

• The data volume of a business data table does not expand due to historical version records. Because historical data is recorded in another table, the business data table only records one copy of data.
• The Schema of the business data table does not need to be adjusted. Additional version fields are added. Because the Schema of the original data table is not changed, the original query logic does not need to be changed. For an existing database design, it is easier to add the history data record function.
• Business data tables can be directly updated without generating a new ID. Because the ID will not change, we need to apply the business primary key to the program logic.

To use a history table to record historical versions, you must modify the data operation methods (add, delete, and modify) so that each data operation first marks the history table, then perform data operations. In addition, the query History version function is modified. Because the history data is in another table, the SQL statements are different. Of course, we can also create a database with historical versions, which stores all historical tables.