OK, so I’ve made a few changes to the database schema. I’ve simplified it a little by taking the stateHistory table out and simply pushing those values into the perfHistory table. Eventually, I’ll be adding a dimensional model data warehouse to this project and I’ll split these types of measures up there instead (so I can track duration of state better). Also, I took out the circular reference to the channel and tied it only to the measure itself instead of the device. I can no longer reuse the channel definitions but that wouldn’t be a very common occurrence anyway.

Here’s the updated schema:

database schema

I also took out the identity field for the history table since it was really unneeded.

Like this post? Subscribe to my RSS feed and get loads more!