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.
Criteria are:
- 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))
with
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?