Database design
Posted: 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?
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?