I'm thinking about using an Event Sourcing / Event Store pattern in my RDBMS designs.
What I mean by Event Store is a pattern where each data mutation (INSERT / UPDATE / DELETE) is persisted as an INSERT into a suitable table of events. At the same time, hopefully in the same transaction, some piece of logic updates a state table with the result of applying the mutation.
This is similar, but conceptually the opposite of what is traditionally done: first executing the mutation (INSERT / UPDATE / DELETE) on the state table; and then executing an INSERT into a log or audit table.
The advantage of this inverted or Event Store approach, as I understand, is that by construction it allows you to query any past state of the system, at any point in time; to undo most types of changes that might have been wrongly applied, for instance by an application bug, without affecting later events performed by users; and so on.
I've read a few articles about this pattern, but all of them seem to use an opaque JSON blob as the event data. This IMHO violates all that is good about relational databases.
I'm more inclined to model events as dedicated tables, using common sense to group similar events into the same table, and using something like DB triggers to perform the mutations on the state table.
Here is an example of what I'm thinking.
Let's take a "shopping cart" example, where the possible events are:
- The user adds a certain quantity of a product to their shopping cart. (ADD)
- The user updates the quantity of one of the products in their shopping cart. (UPDATE)
- The user removes all quantity of one of the products in their shopping cart. (REMOVE)
- The user's shopping cart is emptied, for instance after successfully creating an order. (EMPTY)
In this trivial example, all 4 events may be successfully modeled with a single cart_event table (in most real-world cases, I suppose they would not):
| Column |
Type |
Null |
Notes |
| event_id |
int |
N |
Global sequential id, common to all event tables |
| timestamp |
timestamp |
N |
Event timestamp |
| user_id |
? |
N |
FK to users |
| event_type |
enum |
N |
ADD, UPDATE, REMOVE, EMPTY |
| product_id |
? |
Y |
FK to products, required iff. ADD, UPDATE, REMOVE |
| quantity |
int |
Y |
Quantity to add or update, required iff. ADD, UPDATE |
I'm thinking that the event_id should come from a global sequence, so that all events (in all event tables) can be read in a unique sequential order.
A view may be written with all event types in UNION ALL from all event tables, to browse them in that order. Alternatively, table inheritance may be used, from a global event table, if the RDBMS supports it (they usually don't, or at least they don't support polymorphic queries.)
In any case, the application code would only be allowed to perform SELECT and INSERT into event tables.
Then, AFTER INSERT triggers for all event tables would "apply" the event to a "state" table, which would take the place of the traditional mutable table in SELECT queries. Application code would never be allowed to modify this table, only using it for querying the current state of the system. (The DBA would be allowed to perform mutations on event tables to fix bugs, and then rebuild the state tables.)
Alternatively, a VIEW may be used instead of triggers to create the state table virtually, if the RDBMS is powerful enough to provide decent performance when querying (I doubt it.)
Does this approach have a name?
Is there existing literature about it?
