I have an embedded system with an sqlite database.
How should I define the table structure for the measured data from 4 sensors?
Each sensor give us every second an numeric measured value.
The measured value should be stored with an timestamp in the database.
- Table are “fill only”. There are no delete and update operations.
INSERT (timestamp, value)should be fast.
SELECT (timestamp, value)for an specific time range should be fast.
First Approach: One table for all sensors
CREATE TABLE Measured_Values ( id INTEGER PRIMARY KEY AUTOINCREMENT, sensor_id INTEGER NOT NULL, timestamp DATETIME NOT NULL, value REAL, FOREIGN KEY (sensor_id) REFERENCES Sensor_List(id))
CREATE TABLE Sensor_List( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)
Second Approach: One table per sensor
CREATE TABLE Measured_Values_Sensor_ONE ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp DATETIME NOT NULL, value REAL) CREATE TABLE Measured_Values_Sensor_TWO ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp DATETIME NOT NULL, value REAL)
and so on.
OR is there another better design?
Which design is usually used?
Which design gives me small insert time but also fast query time?
Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex.
When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient.
For fast queries on time ranges, you need an index on the
This implies that every INSERT also needs to update the index.
(If you want fast queries on time ranges for a single sensor, you need another index that contains both the
Please note that you do not need AUTOINCREMENT when you never delete rows.
(Autoincrement In SQLite)