Here is the situation:
- A large amount of time series data from a very limited set of sources is to be kept in a SQLite database file.
- Time series sources are identified by a 10 character long string. The number of sources is very low and not at all dynamic.
- There is no immediate need for any correlation analysis between the time series sources.
I guess, conventionally, one would dump all time series data in a single table with a text field identifying the source of each data point.
However, I am wondering whether it would be more efficient to create a separate table for each source of time series data? Will the file size of the SQLite database eventually be much smaller by eliminating the repetitive text field? Will queries run much faster?
Or should I completely refrain from creating a table for each source and for what reason?
The database will not be huge, which may warrant the use of SQLite over MySQL. Apart from the integer timestamps, there are four more integer fields. Measurements are made every 3 minutes, but it could easily run for a year or more. Part of the design might end up in an embedded device with a very limited amount of flash memory (64Mbit), hence my initial interest for SQLite. However, I am open to other suggestions.