Database design

Pop your questions regarding Home automation software here.....

Database design

Postby gjelsvik » Thu May 13, 2010 6:47 am

Hello.
First post here, so I might just spend a line or two to introduce me. I`m 25 years old boy from Norway, works as a IT consultant, mainly windows stuff, but spends some time with linux as well. I have a house wich I slowly try to make a "smarthome" as we say here in Norway.
It all started by manuelly readingmy electricity meter everynight, typing into excel and making charts there. Then It went to a simple php/mysql solution. Then i created my own pulse-reader from a arduino controller, wich uploaded data to my mysql DB.

Now however, I have a setup with homeseer,rfxcom, nexa units, and now looking to buy a plugwise kit. And here is where my Database question starts.

I have played with VB scripts in homeseer wich will upload device values into mysql on my hosted webserver. This is to have a redundatn solutions, so that i dont loose all my historical data when/if Homeseer stops there service. And also to be able to draw my own graphs outside ASP.

I want to upload data from the Rfxmeter:
every 6min i want current usage to table'a
every 1hour i want total usage to table'b'
every 24h i want total usage to table'c'
Every 30day total usage to table 'd'.

And for the plugwise, i have not yet decided what i want. I have seen the charts from Pieter at the bwired site.I want something like that, where i can see the total used by every circle.

For the temperature, I will read thisby an Arduino onewire-to-ethernet controller i made. It will do all the uploading by itself.

How woudl you guys do the database design? Wich fields in wich tables?
gjelsvik
Starting Member
Starting Member
 
Posts: 9
Joined: May 2010

Re: Database design

Postby Digit » Thu May 13, 2010 11:28 am

Hi gjelsvik,

Welcome to this forum and i hope you can find some useful information here.

You've forgotten a few tables, for every year total usage, every decade, ...
Just kidding, but why use multiple tables for historical data?

Ok, it will make the query you need in order to format the results in the way the chart control needs them, easier to make.
But from what i read this database is already redundant (or a backup); by adding more tables you add even more redundancy.
In the case of an error in the stored data (and that will happen!) you will end up correcting multiple tables. No fun.. :(

In my case i have a single 'data' table for all historical data, with the following fields: datetime, deviceID, startvalue and endvalue. (you need to know that for example with Plugwise i use different (logical) DeviceIDs for Total and Current Power Usage)
With that and the appropriate GROUP BY, SUMs etc. in the query you can make any chart you wish; either a line chart of temperatures or CPU usage, or hourly, daily, monthly or yearly bar charts of whatever you store in the database.
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby gjelsvik » Thu May 13, 2010 7:33 pm

Thanks for your reply!

I want to split it up, because the one containung current usage will be trunked (only contain last 30days os so)
What did you mean with startvalue and end value?
I was thinking that one line in the db would containing device_id, value, timestamp. (This is what i am doing now, but find it hard to make the queries to get average per hour, day, or month.

Would you also have electricity readings and temp readings in same table?
gjelsvik
Starting Member
Starting Member
 
Posts: 9
Joined: May 2010

Re: Database design

Postby Digit » Thu May 13, 2010 9:35 pm

Start- & endvalue are used because i don't store delta's but i also don't want to use 2 records to calculate a value for 1 time period.

Yes, my historical data table contains temperatures, humidity, power usage, gas, water, visonic signal levels, pressure, lux, etc. etc.; everything i find interesting enough to keep. Currently this table contains 326000 rows in 115 MB.
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby Bwired » Thu May 13, 2010 10:08 pm

Here you can find some more discussions about it, gives you some more ideas.
viewtopic.php?f=23&t=179&start=0
User avatar
Bwired
Administrator
Administrator
 
Posts: 5315
Joined: March 2006
Location: Netherlands

Re: Database design

Postby gjelsvik » Thu May 13, 2010 10:26 pm

Hmm could you explain more detailed how you have start and end values, and how you use this for. e,x temperature?
I mean, if i log temp one time each hour, what would you use both fields with the same value? I cant figure what u would use as start and end, since the timestamp represent a point in time, not a period of time given by two timestamps, right?

I have thought about the database design somemore, and decided to use only one table for my HA setup, (no need for one table for each timeset like hour, day, month and decade :-) i think. And only one table makes the backup-job easier.

I would like som feedbackont this field-setup in the table:
id(autoincrement),timestamp(datetime), devicename(text20),type(text20),current_val(float7), total_val(float10),source(text20)

Example input would be this:
1,2010-05-13 21:30:32,mainpower meter,rfxpulse,2199,32167,Homeseer
2,2010-05-13 21:35:00,Inside temp,oregon-temp,22.3,,1Wire

Theoretically it shouldnt be a problem mixing all types of readings in a database, but will it not be hard to filter out the right result in your queries when everything from temperature to lux is stored in the same two fields(start_value and end_value)?

I read through that topic Pieter,but it did mostly containt how to get the datasetout of query?
One interesting thing you wrote there though, You said you logged each minute, but only created one record each hour,wich was updated every minute.How is that possible?
gjelsvik
Starting Member
Starting Member
 
Posts: 9
Joined: May 2010

Re: Database design

Postby Bwired » Thu May 13, 2010 11:06 pm

On the other part Robert will answer.
I use 1 record for every hour otherwise I get to much records which are not important to me.
It is simple, once a reading comes in I check if for that date/hour a record is in the database.
if not I create a new hour record with the begin- and end counter. If the record is in the database I just update the end counter field.
This way I can see per hour my usage and don't need the query the database that much.
With some other queries its easy to do day/week/month etc.

Check the results on Bwired:
Energie: http://www.bwired.nl/Stroom.asp
Gas: http://www.bwired.nl/Gas.asp
Water: http://www.bwired.nl/Water.asp

Greetings from France
http://www.bwired.nl Online Home, Domotica, Home Automation. Weblog. http://blog.bwired.nl
User avatar
Bwired
Administrator
Administrator
 
Posts: 5315
Joined: March 2006
Location: Netherlands

Re: Database design

Postby Digit » Thu May 13, 2010 11:24 pm

The 2 fields are specifically for storing 'raw' counter values and less useful for absolute values like temperatures and other stuff.

However, when i log a pressure value with a 1 hour interval, the startvalue will be the 1st value measured in that interval and endvalue will be the last measured value.

Actually, it's all due to non-device-specific methods of storing historical data... i have 1 class method that takes care of storing historical data. This method doesn't know whether it's storing temperature, power usage or whatever..

What Pieter wrote:
I use 1 record for every hour otherwise I get to much records which are not important to me.
It is simple, once a reading comes in I check if for that date/hour a record is in the database.
if not I create a new hour record with the begin- and end counter. If the record is in the database I just update the end counter field.


That's exactly the same as i do.. with a device-configurable interval.
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby Digit » Thu May 13, 2010 11:33 pm

gjelsvik wrote:I would like som feedbackont this field-setup in the table:
id(autoincrement),timestamp(datetime), devicename(text20),type(text20),current_val(float7), total_val(float10),source(text20)

That looks OK to me. Try to keep recordsize small since you'll get a lot of them :D
Do you need an autoinc field? Do you really need source field every time? Can't you store it elsewhere? The same goes for the type field.

gjelsvik wrote:Theoretically it shouldnt be a problem mixing all types of readings in a database, but will it not be hard to filter out the right result in your queries when everything from temperature to lux is stored in the same two fields(start_value and end_value)?


In practice it won't be either.
Just include a WHERE in your query to limit the result to a specific devicename.
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby gjelsvik » Fri May 14, 2010 8:15 am

Now I slowly start to understand your way of thinking, guys.

Its only about to make som logic into the function that write the entry into the table. Because you will like to search up last entry from that specific device first to see if it allready exist.

Thanks Robert and Pieter, this have "cleared my head" and if this is the way you do it on the bwired site, i think that tells me that is a solution that works :)

Gonna write som php now, and see if I can manage to get this done.
gjelsvik
Starting Member
Starting Member
 
Posts: 9
Joined: May 2010

Re: Database design

Postby Digit » Fri May 14, 2010 10:50 pm

Good luck and keep us posted on the results! :)
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby gjelsvik » Sat May 15, 2010 9:52 am

I have it up and running now.
Homeseer uploads the rfxmeter value on every devicechange. Producing one row each hour. with start and end values.

One question for you again. On the site, ( http://www.hekkers.net/domotica/DeviceStatus.aspx ) I see the value for each sensor can be anything from a temperature, a text string, a number etc etc.. What is your datetype in the database for the start and end values ?

I have now chosen "float" as dataype, this works great for kWh, but maybe not for a motion detector wich value would be "motion" and "nomotion"

S workarounf would be to use numerical values for every device. How did you do this?

Current database:
Image
gjelsvik
Starting Member
Starting Member
 
Posts: 9
Joined: May 2010

Re: Database design

Postby Digit » Sat May 15, 2010 6:26 pm

Great! :)

The values on DeviceStatus page are not coming from the 'data' table.
Since i have like 478 items monitored/measured but don't keep history of all those items, i can't use the data table as source for this page.

For that i created an additional table, called 'devicestatus', which always contains the last known value of each item. This table looks like this:

CREATE TABLE [DeviceStatus](
[DeviceID] [varchar](25) NULL,
[nValue] [numeric](18, 6) NULL,
[cValue] [varchar](1000) NULL,
[LastChange] [datetime] NULL CONSTRAINT [DF_DeviceStatus_LastChange] DEFAULT (getdate()),
[LastUpdate] [datetime] NULL CONSTRAINT [DF_DeviceStatus_LastUpdate] DEFAULT (getdate())
)

Just regard the Value column on the page as being the cValue field in the table. This is not completely true, but for now this will do.

Tip: you can store 'motion' and 'no motion' in a bit field (or a numeric field) as 0 or 1, and define what the 0 or 1 means elsewhere. The same applies to light & dark, open & closed, on & off, etc. Even Oregon sensor values like Normal/Comfort/Dry/Wet can be stored as a numeric value in this way. :wink:

HTH
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby gjelsvik » Sat May 15, 2010 9:22 pm

I liked that idea.. So i did like you..created new table and updated my script so that it updates the device_status table everytime the datalog table is updated.
gjelsvik
Starting Member
Starting Member
 
Posts: 9
Joined: May 2010

Re: Database design

Postby gjelsvik » Sun May 16, 2010 7:06 pm

Have made som interface for my databases now, with graphs.

I kinda feel that I still have the need for a historical version of the devicestatus database.
I have the 'datalog' table wich have power records by hour. This gives me powerconsumption, wich is ok with 60min interval.
I have the 'devicestatus' wich gives me last status of the temp sensors, and current power usage.

I _think_ i also need a table wich holds temperature, and current power usage with 6min intervals.
Like now, i started logging my freezers inside temperature. And I see it peaks up and down because its bad insulated.
With plugwise modules later, it would be great to see the linechart with temp and power together.

http://gmx.no/strom/fryser.php
gjelsvik
Starting Member
Starting Member
 
Posts: 9
Joined: May 2010

Next

Return to Questions & Discussions Forum

Who is online

Users browsing this forum: No registered users and 1 guest