I am trying to design my database and I want to have the ability to keep a track of history of changes.
I will have a table that will have all the nutritional facts of an food. When a user makes changes to the item(say changes calories from 100 to 200). I want to make that as a new revision.
That way a person who comes along can see that it was original 100 calories and then was updated to 200 calories. I guess this would be alot like how stack does it now where you can see what has been edited.
I am wondering what is the best way to do this? I am using sql server and nhibernate.
I was thinking of have another column or something that would be revision number. Then every-time a revision is made the number is incremented. Is this a good way?
NHibernate.Envers helps you with that.