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

Answer 1

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.

Answer 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.