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:

image

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