Event Sourcing – Part 2 – What do we track?

We decided that we will be modeling a fictitious application called “Patient Tracks” and that we were going to use Event Sourcing backed by MS SQL.  We will take the simplest approach possible at first and then add complexity over time to the application.  The very first thing that we will start to model is the [Floor] of the building.  The [Floor] will be a container that holds other things that the [Patient] will interact with and that we will track.

Pick a place to start

Starting with the [Floor] it would be fairly easy to model this as a traditional table but our [Floor] could change over time and it would be interesting to keep all of the relevant change events that would make it up over time.  When I think of events, I think of them as two distinct types of which the first is a DATA event, where a data element is set or changed and the other event type is a MODEL event.  A MODEL event is any event that is interesting but does not explicitly set or modify the data elements of the item that we are modeling.  This may not fit the more traditional thoughts that are out there on Event Sourcing but we will move forward with this assumption.  My goal is to make it very clear and easy to model the current state of the data later on and to still have a way to emit interesting events for other things that could happen to the model but do not change it’s “DATA” state.

What is an Event?

We will define an event as anything that happened to our model in the past.  Simple enough, we have accounted for time, the noun that was acted on and we have the event that occurred to the thing.  If we can define those things, we should be able to answer quite a few questions that could be asked of the data.  We should begin to define the structure of how these items will be stored and establish the start of a contract for these data elements.

What Events could our [Floor] have?

We could think of a few events that could be emitted and stored for our [Floor] model:

  1. Floor Created (i.e. floor.created)
  2. Floor Data Modified “Name”  (i.e. floor.data.name)
  3. Floor Container Added X
  4. Floor Container Removed X
  5. Floor Deleted (i.e. floor.deleted)

Seems like a decent start for tracking what could initially happen to a [Floor] in our application.  Later, we could put “business” rules around when certain events can occur and prevent or automatically perform other events based on the rules.  We will shelve that complexity for now.  Now we need to persist this data in the database.

Storing the [Floor] in the database

Now things start to get a bit more complex as we reason about how to store the data in the database.  We want to err on the side of simplicity but the database schema should be flexible enough that we could reuse some elements.  We will model what we need for our [Floor] now and then refactor the reusable parts out to provide more flexibility.

We will put together a table to store the definition of the [Floor] model and we will use a terrible dot notation for our naming convention on the fields in the SQL tables.

CREATE TABLE [dbo].[model](
  [model.type] [nvarchar](50) NOT NULL,
  [model.event] [nvarchar](255) NOT NULL,
  [model.key] [nvarchar](50) NULL,
  [model.value.type] [nvarchar](50) NULL
  • model.type == is the type of object that is being modeled (i.e. Floor)
  • model.event == is the name of the event that happened to the model
  • model.key == is the key name that will be applied to the key value pair of the data.
  • model.value.type ==  is a text field that describes the type of data that will be in the value field during programatic validation.

We will keep this concept moving forward in small steps but will pick up speed soon.  The above will just serve as a model definition for the “thing” and the changes over time.  The changes will be stored in a table for each event that has occurred on each of the instantiated models.  In a future article we will define the model in the table structure and store the events that make up a [Floor].


About b.hedge

i do database stuff and i like to solve business problems with code.
This entry was posted in cqrs & event sourcing, mssql. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s