Hi All. I am new to sqlite and SQL in general. I am keen to switch from flat-files to sqlite for holding some measurement information. I need a tip on how to better layout the database, since I have zero experience with this.

I have a ~10000 unique statistic counters that are collected before and after each test iteration. Max number of iterations are 10, though it could be less.

I was thinking the following:

CREATE TABLE stat_names(stat_id, stat_name);
CREATE TABLE stats_per_iteration(stat_id, before_iter_1, after_iter_1, before_iter_2, after_iter_2, ...);

stat_names table would hold mapping of a full counter to a uniq stat_id. stats_per_iteration table would hold mesurement data 1 + 10 * 2 columns. stat_names.stat_id = stats_per_iteration.stat_id

OR maybe I should have a separate table for each iteration? Which would results in 1 + 10 tables in database.

Thanks!