How to implement temporal data in MySQL
Make the given table R temporal(ie, to maintain the history).
One design is to leave the table R as it is and create a new table R_Hist with valid_start_time and valid_end_time. Valid time is the time when the fact is true.
The CRUD operations can be given as:
INSERT
- Insert into both R
- Insert into R_Hist with valid_end_time as infinity
UPDATE
- Update in R
- Insert into R_Hist with valid_end_time as infinity
- Update valid_end_time with the current time for the “latest” tuple
DELETE
- Delete from R
- Update valid_end_time with the current time for the “latest” tuple
SELECT
- Select from R for ‘snapshot’ queries (implicitly ‘latest’ timestamp)
- Select from R_Hist for temporal operations
Instead, you can choose to design new table for every attribute of table R. By this particular design you can capture attribute level temporal data as opposed to entity level in the previous design. The CRUD operations are almost similar.