Design pattern good for many entities history?

Question

I have some entity classes in my application. I want to save all changes to this entities and some happened events in a history table. Problem is that entity classes are different, build with different primitives and different relation between them.

For example in a shopping application there can be: User, Item, Transaction, Watch. I want to be able to show some activity log to user like this:

• 16:00 You've bought "Lord of the Rings" in a transaction 2468,
• 15:30 You've added "Hobbit" to your watchlist,
• 15:00 Watched item's "Silmarillion" has dropped price from 15 to 12,50 euro,
• 14:30 You've changed your name from "Tomasz" to "Tom".

There are many things involved in this log:

• new Transaction entity 2468 with Item "LotR",
• new Watch entity linked with Item "Hobbit" and my account,
• updated Item price (but both old and new prices are stored),
• updated User name (both old and new stored).

My main question is how to keep track of these data?

1. Should I have as many tables as many entity tables to keep their changes? UserVersions, ItemVersions etc?
2. Should I query all Version tables, join and sort results to generate this log?
3. Or maybe there should be one table Versions with columns "Entity", "OldValue", "NewValue" - what about constraints and foreign keys? Isn't it too dirty solution?
4. Is there design pattern for this?
5. Finally if you know - how does Facebook does this? :)

Answer 1

You might be interested in design pattern called Event Sourcing.

Event Sourcing ensures that all changes to application state are stored as a sequence of events. Not just can we query these events, we can also use the event log to reconstruct past states, and as a foundation to automatically adjust the state to cope with retroactive changes.

As for how to store such events, it actually depends on the requirements. Sometimes it is enough to simply serialize it to JSON.

Event Sourcing is included in most implementations of CQRS, so you can find additional information in almost any resource related with it.

Answer 2

You could use the commandpattern. If you add them to a stack you have your history.

Each command has all the information needed for carrying out its task. This off course means that each command is disctinct and it is quite difficult to store and query them for a specific users view (because the user needs to see info from commands invoked by others (like 15:00 Watched item's "Silmarillion" has dropped price from 15 to 12,50 euro).

You can represent the command as follows in the database:

Command
-------
id
name
timestamp
user

CommandParameters
-----------------
commandId
name
value

Now you can build a query that shows all relevant command for a user or a specific item the user has in its watch list (you could add a table containing all commands relevant for a user and item)

HistoryBuilder
--------------
viewName
commandName
filterField

Where for user you get all command that could be executed by end-users and for items you get all commands that are related to items like price updates stock changes etc.

Now you can provide adapters to make more user friendly messages from those commands. In this way you can use the same commands in other contexts with another adapter over it.

I hope this gets you any further.

Answer 3

Maybe you could use an approach that Drupal and some other CMS'es take, that is to keep current and older versions of the same data in a single table. Described in more detail for example here: CMS versioning strategies for content

Don't really know what is the name for this pattern, but I'm sure it earned itself one.

This has an obvious advantage of speed. In terms of keeping track of operations that introduced each of those changes you could use a separate table, possibly with some links to records before and after the change.