Mysql database structure

Pop your questions regarding Home automation software here.....
Post Reply
psmaaswinkel
Member
Member
Posts: 88
Joined: Wed Oct 27, 2010 8:36 pm

Mysql database structure

Post by psmaaswinkel »

From THE beginnend of this year I am logging my homeseer sensorvalues in a mysql database for website usage.
THE structure is straight forward at this moment. I am using à single table with the devicegroup, devicenumber, time and value.

Because I like to keep things efficient I would like to know what is the best structure for storing the data. I don't feel the current layout is the best choice...

Any ideas?
___________________________________________________________________
iEase Home Control
Digit
Global Moderator
Global Moderator
Posts: 3388
Joined: Sat Mar 25, 2006 10:23 am
Location: Netherlands
Contact:

Re: Mysql database structure

Post by Digit »

Why do you think your current setup is not the best choice and what do you want to improve?
It's a bit too vague to me to come up with ideas.
For instance, when I see devicegroup & devicenumber, I wonder if it's only the combination of the 2 that makes a row in the table relate to a specific sensor, or does the devicenumber already imply a devicegroup? Or, IOW, is the devicenumber unique? And do you carefully pick the right time interval between sensorvalues you store; for example, it would not be smart to store every sensorvalue (change) received from an Oregon Scientific Temp/Hum sensor (almost every 40 seconds).
So tell a bit more about your current layout, that will help to get useful replies (not like this one :wink: )
soitjes
Member
Member
Posts: 67
Joined: Wed Feb 10, 2010 10:35 pm

Re: Mysql database structure

Post by soitjes »

With such simple table I'm pretty sure that you will quickly run into performance problems. I also use such a table where I store all the raw values I receive from devices, but every minute, hour and day I calculate the average, total, min and max values for that respectively minute, hour or day. Those values go in separate tables (called cache tables). I use a feature from the database engine to do that in separate threads, so that the main table is always available (and so my application doesn't lock while running those queries).

Now let's say I would like to plot the temperature of all rooms during the last month : instead of going over all the records for all temp devices I simply read the pre-calculated value of each day of the month, and plot it. That is max. 31 records per device in a simple SELECT query, instead of a several hundreds in a complex SELECT with subquery and aggregate functions. Imagin that you do the same for a whole year...

Obviously, not only the database will become more complex, but also your application code. Because to plot the current month I have to read from the cache tables until yesterday, and for today I can only use hour and minute cache values and for the last minute I have to use the raw values. But like any decent algorithm : a lot of code does not mean slow code :) All my charts are almost instantly plotted.

Soitjes.
My Digital Home Server - http://www.digitalhomeserver.net
Post Reply

Return to “Questions & Discussions Forum”