I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get aggregated through a process of copy-paste and buggy macros.
My intent is to create a simple python script which aggregates all of the data for an experiment into an SQLite database and then produces the requisite CSV/XLSX output.
My issue is that for a single trial of our experiment, we end up with about 100 variables recorded at about 10 different time points. My initial impulse was to create a
CREATE TABLE value (val_id INTEGER PRIMARY KEY, value TEXT, var_id INTEGER, event_id INTEGER, exp_id INTEGER, FOREIGN KEY (var_id) REFERENCES variable(var_id), FOREIGN KEY (event_id) REFERENCES event(event_id), FOREIGN KEY (exp_id) REFERENCES experiemnt(exp_id) ); CREATE TABLE variable (var_id INTEGER PRIMARY KEY, var_name TEXT, var_type TEXT ); value: val_id | value | var_id | ... 0 | 10 | 0 1 | "ROSC"| 5 variable: var_id | var_name | var_type 0 | Pressure | DECIMAL ... 5 | Outcome | TEXT
But this feels wrong and I have a hunch at the "proper" way to do this is to have a single data table with the hundreds of columns that would otherwise be described in the
variable table since this would make it easier to do type checking (yes, I know SQLite doesn't do this, but in principle).
Any insight into how to tackle this would be hugely appreciated.