Educational Administration System database improvement-resolution of historical record Problems

The biggest problem with the previously designed database is that it cannot display the status information of the past time, but only records the operation records of a single information. So there will be some problems, for example, there are 32 people in the class in 1st day of last month, and a person dropped out a few days ago, then the information of this class is 31 people, there is no way to display the previous 32 people's information. This is a history, but we cannot display it.
Solution 1: From the perspective of students, the institutions in the system are set up for students, which is equivalent to a shelf for students. Therefore, students' information can be changed at will, when dealing with records similar to classes and majors, adhering to the principle that it is related to other entity classes or contains elements such as students below, it is considered meaningful and cannot be deleted. This record is meaningless and can be deleted only when there is no link and no elements exist. The starting point is: student-centered. The main problem is that the changes of students will affect the previously stored information, and historical data is not actually retained. (Rejected)
Solution 2: Similar to archive management, a new structure is the same as that of the original database. A record is changed, and the related contacts are recorded once with a timestamp added. Such an idea can display the information at a certain time in the past, restore the information, and card the information according to the time. However, there is time, but it is a problem to find the relevant information at that time. After all, it will take some time for the database to process the data, it is unrealistic to rely entirely on time. (Rejected)
Final Solution: The same tables in the database are composed of two tables, one storing the latest status information and the other recording information changes. In this design, the concept of lifecycle is adopted. Each record has a validity period and expiration time field, that is, adding a record to the database is the beginning of the lifecycle, modifications and other operations between them only affect the statuses of different time periods in the life cycle. Deleting is the end of the life cycle. No record is actually deleted. A field is used to identify whether the information is available. If you make a change to the record in the current information table, add a record to the History Table, change the expiration time to the current time. When you create a page, you can create a maintenance page for the current information, which can be added, deleted, and queried using the current data table. On the history query page, the retrieved information cannot be operated. to query data, you must use the combined query of the History Table and the current table. Everything is limited by time, and information changes also come along with time. Any entity has its own unique state at a certain time, in this way, you can query the status information at any time.