For our application, we will be creating two tables, one each for our temperature and humidity readings. The structure will be similar for both of them:
- The createdAt column to store the date and time of creation
- The value column to store the actual value read by the sensor
When creating the table, it's useful to know SQLites datatypes. This will not be a hard task since there are only five:
- TEXT
- NUMERIC
- INTEGER
- REAL
- BLOB
In most other relational databases, there is a data type for datetime, which we would have used for our createdAt column. In the case of SQLite, the datetime is represented either as a string in the ISO format (YYYY-MM-DD HH:MM:SS), or as an integer in Unix time (the time elapsed since 1970-01-01 00:00:00 UTC).
From this, we decide the types of these:
- createdAt as TEXT (ISO formatted datetime)
- value as REAL
Start up the SQLite shell and enter these commands to create your temperature and humidity tables:
CREATE TABLE temperature (createdAt TEXT, value REAL);
CREATE TABLE humidity (createdAt TEXT, value REAL);
To see the list of all tables available, run the following command:
.tables
This would give you an output of :
sqlite> .tables
humidity temperature
There are a few important reasons why two tables for temperature and humidity were chosen instead of just one table with the createdA, temperatureValue, and humidityValue columns:
- Even though the single table model represents the way by which we receive readings now (currently, we obtain both temperature and humidity readings at the same time, so the createdAt value would be identical for them) , that may not be the same in the future. If you have noticed, we have separated the functionality for temperature and humidity in all the other chapters including this one because this way, we can also handle cases where the readings are taken separately, through different sensors.
- We make room for more readings in the future. If we are required to measure another quantity in the future (such as the intensity of light or atmospheric pressure), we can just add more tables and extend the functionality for the rest of the application, just like we did for temperature or humidity. If we used a single table for everything, it would be difficult to add new columns in the future due to the difference in time of measurement and all the NULL values we would have to account for in the time when they didn't exist.