Structure of a CQRS event store

Question

I'm currently trying to understand how to build the internal structure of an event store. What I got so far:

• An event store has two tables (collections, ...), one for aggregates and one for events.
• The aggregates table contains the following data: aggregateId (which will probably be a GUID) and the aggregateVersion (which is an integer that simply represents the number of the last event that influenced this aggregate).
• The events table contains the following data: eventId (again, a GUID), aggregateId (which the event belongs to), payload, and a version (which is simply an integer that describes the order of the events).

Is this correct so far? Should events be ordered by using an integer? Or should they be ordered based on a timestamp? What are the advantages of each? What are the disadvantages?

Answer 1

I would suggest you to have a look at Jonathan Oliver's EventStore for reference.

In the SQL persistence version there's only one table. You could easily live without the aggregate table and store the aggregateId in the events table. The latest version could be retrieved by using a max() query.

Other than that I think you should consider having headers in the events table, cause there are always interesting metadata that you don't want to store in the events themselves.

And also, I think you should add a date column in the events table.

Finally, you probably want to have some sort of flag that states if the event has been dispatched downstream or not. This addition enable you to write in one thread or process and dispatch in another.

Aaand there, I have sort of suggested the structure of Jonathans EventStore.