Keep historical database relations integrity when data changes

Question

I hesitate between various alternative when it comes to relations that have "historical"
value.

For example, let's say an User has bought an item at a certain date... if I just store this the classic way like:

transation_id: 1
user_id: 2
item_id: 3
created_at: 01/02/2010

Then obviously the user might change its name, the item might change its price, and 3 years later when I try to create a report of what happend I have false data.

I have two alternative:

1. keep it stupid like I shown earlier, but use something like https://github.com/airblade/paper_trail and do something like:

   t = Transaction.find(1);
   u = t.user.version_at(t.created_at)
   

2. create a database like transaction_users and transaction_items and copy the users/items into these tables when a transaction is made. The structure would then become:

   transation_id: 1
   transaction_user_id: 2
   transaction_item_id: 3
   created_at: 01/02/2010
   

Both approach have their merits, tho solution 1 looks much simpler... Do you see a problem with solution 1? How is this "historical data" problem usually solved? I have to solve this problem for 2-3 models like this for my project, what do you reckon would be the best solution?

Answer 1

I'll went with PaperTrail, it keeps history of all my models, even their destruction. I could always switch to point 2 later on if it doesn't scale.