versioning each field vs history date field?

Question

Which do you recommend and why?

I have a few tables, when i make a change to the data... it should go to a history table (audit) with a effective date.

The other solution is versioning each field to insert a new row when making changes to the data?

Which is the best method for the invoice information? Item name and price is always change

score 4 · Accepted Answer · answered Apr 26 '11 at 12:54

4

These are slowly changing dimensions, type 2 and type 4, appropriately.

Both methods are valid and may be more appropriate for your needs, depending on your model and query requirements.

Basically, type 2 (versioning) is more appropriate when you need to query historical values as often as the current one, while type 4 (history table) is more suited when you are querying the current value more often and there are more queries (more queries to develop I mean) against the most recent value.

answered Apr 26 '11 at 12:54

Quassnoi

413,100
91
616
614

I have an order_items to display past orders.. which one is better? The value of items name and price is always change. – user622378 Apr 26 '11 at 13:01
@user: usually (usually) a history table is better, since you need the current price is more important and you run more queries against it. This way, you won't need to add a filter on `isActive` or a similar field to each query. – Quassnoi Apr 26 '11 at 13:03
Instead of DATE in the history table, i added the version field. Example of tables: item.version, item_history.version and the order_table table will contain the version number. So I will get 100% accurate information rather than relaying on the date search. – user622378 Apr 26 '11 at 14:07

score 2 · Answer 2 · answered Apr 26 '11 at 12:57

2

A system we use and happy with:
Each table that requires history, we create a similar table and adding a timestamp field at the end, which becomes a part of the PK.
Each update on original table, we insert into history table with the same conditions:

update table x WHERE somthing something

insert into table x_history 
select * from x WHERE something something

That keeps your data clean and your tables slim.

answered Apr 26 '11 at 12:57

Itay Moav -Malimovka

52,579
61
190
278

There is no problem with accuracy with this solution? For example.. Showing invoice data from the history table (old data).. I hope it show the correct price lol – user622378 Apr 26 '11 at 13:03
Why should there be a problem? You can use versioning, but it involves more logic/code which I find not necessary – Itay Moav -Malimovka Apr 26 '11 at 14:29

score 0 · Answer 3 · answered Apr 26 '11 at 13:11

My personal preference would be to user the Observer Pattern in your application and to implement a separate history table. This means that you can pull the data from the history table when you need it and you don't compromise the speed of querying the main table.

versioning each field vs history date field?

3 Answers3

Linked