Topic: database design and historical datas
I am having a struggle with managing historical datas
I have a table where When a field is change I record the change of one the field and wether this was done by a manual change or by a script(so automated). This change is later on use to make some logic decision in real time. I have 2 possible solution but have a hard time to pick and wonder is there is a better way to do it
let say the table is call myfieldlog with field ID, createTime, From , to, isManual
1)First solution would be to denormalized the table by add a field (Revision) that contain the value current for the current one and archive for the rest
2) Second solution would be to maintain in that table only the current version and create a second table (myfieldloghistory) that contains the archives (so just a table with same fields) so that everytime a change is done, an update is performed on the myfieldlog table and a copy of the old data is pushed to the history table
What do you think is best as an approach?
What bothers me is to have to create 2 table every time I need some type of historical data . But I don t like the denormalized solution as usually denormalized data are harder to maintain(in terms of design evolving)