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:

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

For a while now, I’ve been working on the database design for this project. I wanted to keep it simple and limit the number of tables. In this first design there are a few columns that aren’t normalized, but it helped to limit the table count.

The design is somewhat like a CMDB in that it keeps track of configuration items, their properties, as well as the type of item. Such things as computers, refrigerators, furnaces, and rooms in the house would be considered configuration items.

So the major parts (tables) to the database are as follows:

Configuration Item

configItem – Again, stores information on things like computers, freezers, and rooms.

Location

location – defines where items such as sensors, config items, and measure devices are physically located.

Sensor

sensor – These are specific sensors located in the house, they may be as simple as a thermistor or as complex as an entire script that lives on a computer.

Measure

measure – These are specific measures and the values associated with them.

Measure Device

measureDevice – This table stores the various physical devices the measures are taken with. For example, the sensor might be a thermistor, but the measure device would be an analog to digital converter that measures the voltage across a voltage divider.

History

(perfHistory, stateHistory) – There are two tables here, one for performance values such as temperature, humidity, free physical memory and the other for state values like light on, motion detected, and door closed or open.

So far, I’m not including any aggregate tables for data summarization or any user interface tables…I’ll leave those for later.

Here is the database schema: