Updating variables in xquery
Avoid Design 1; it is not very handy once you will need to for example rollback to old versions of the records - either automatically or "manually" using administrators console. I think the second, History table should contain all columns present in the first, Records table. in mysql you can easily create table with the same structure as another table ( Don't forget to use transactions!
This way you can store all version of employees in one table.This will slow the process and also add some limitations like we cannot add joins on the revisions data fields.And the problem with the "Design 2" is that we have to duplicate each and every field on all entities (We have around 70-80 entities for which we want to maintain revisions).If you need to track the actual historical (and/or future) versions, then the standard solution is to track the same entity with multiple rows using some combination of start, end, and duration values.You can use a view to make accessing current values convenient.
Search for updating variables in xquery:
We use the Record ID field to store the value of the key field of the table being updated.