Database design to hold multiple iteration measurements

Question

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!

Answer 1

It really depends on your needs, which really are not addressed in your question. When designing tables look at these factors, in no particular order:

• how will you write the data
• how will you read the data
• will space be an issue

These can determine their order of importance:

• how many times will you read the data, many or few?
• will you need to make writes with a high frequency?
• how complex are your read queries?
• will the data fit in the space you have?

Based on those, design your structure to make your life easy. In general:

• If you have no space to store it, design it so it will fit
• If you need fast writes, design it so you can output many rows quickly (simple flat design)
• if you have to manipulate and analyze the data a lot, store it in a way that makes that easy.

Answer 2

This may get you started. Your stat_names is called Measurement and stats_per_iteration would be the Iteration table. The Test table would have one entry for each test performed -- like for new device, new day etc.