Designing database

Hi, I've finished my build of controlling water heater temperature and I'd like to enter the data into database by date, time and temperature. Problem is that I've never designed a database even I'm quite familar working with them. Basically I have two 'problems' to solve.

  1. In which way the database should be built for it to be easilly accessible (like searching for min/max temp on a certain date or how to query the heating time of a date.

  2. Should the sql writes/queries made by python (Python interacts with Arduino) or by PHP? PHP is probably easier as it would be cool to have a web page where you can see the current temperature and control the servo which turns on/off the heating element and to accessing different info from the database.

Your database could be as simple as a single table with a date time field and a numeric field to hold the temperature. Put an index on the date time field, job done.

If you think you might ever want to collect temps for another tank or have more than one sensor per tank, you might want to add tank_id and sensor_id fields, but it seems like overkill in this situation.

As to Python or PHP - whichever you prefer.

Database design depends on what a record is.

Sounds like each record is a point in time. So a recordid, datetimepoint, temp, other fields like tank#, etc.

You'd have to explain more about "heating time"

max temp on a certain date might be as simple as
select max(temp)
from table
where date = selected date

PHP and mySQL is a very common/popular solution.

SqLite is even easier to use than MySQL if you don't need the "professional" capabilities of MySQL.

I use JRuby (rather than Python) and there is an add-on (a Gem in Ruby-speak) called Sequel that makes it really easy to access SQL databases. I suspect there is something equivalent for Python.

Presumably the web-app could be implemented with Python.


do you even need a database?

A flat CSV file and any spreadsheet software (even Google Docs) can provide you the same search/summary functionality.


I use SQLite + Python for my weather station database/website.
SQLite was easy to install and use.
You can find alot of tutorials if you google -> python sqlite

My server OS is Linux but if you have a Windows server I recommend SQL Server Express which is free.


You need to do a number things,

first write software to take data from the aduino and put it in you datadase so what format are you going to send it in?


Problem is that I've never designed a database

I'm not sure now is the time to start and you are at risk of trying to re-invent the wheel. Assuming that the current output of your build is numeric data, all you need do is feed that to Excel instead of your serial monitor. No need to send date and time. You can do that by using PLX-DAQ, a free Excel macro. This effectively turns Excel into a terminal, thereby allowing you to feed the data directly, and allows spreadsheet and graphs to be updated live.

I am reasonably familiar with spreadsheets and MySql/SqLite. A spreadsheet is fine if you don't have many rows of data - say less than 100. They can hold thousands of rows but it quickly becomes very inconvenient to manage them and very easy to make errors.

The big difference between a spreadsheet and an SQL database is that the spreadsheet identifies data items by where they are located in the grid and formulas often depend on the order and location of data. Whereas sql identifies the data by the names of the columns (which are not part of the content of the table) coupled with the actual values in the database. OK you can write complex formulas in a spreadsheet to give the impression it is working with named columns - but at its heart it is a row x column product. And if something gets moved to another place it won't work properly. That can't happen in an SQL database.

SQLite is very easy to use - it's just a single file (usually nnnn.sqlite) in your project's directory.