I am designing a database to record experiment results. Basically, an experiment has several input parameters and an output response. Therefore, the data table will look like following:
run_id parameter_1 parameter_2 ... parameter_n response
1 ... ... ... ...
2 ... ... ... ...
. . .
However, the structure of this table is not determinant since different experiments have different number of columns. Then the question is: when a user instantiate an experiment, is it a good idea to create data table dynamically on the fly? Otherwise, what is the elegant solution for that? Thanks.
When I find myself trying to dynamically create tables during runtime, it usually means I need another table to resolve a relationship between entities. In short, I would recommend treating your input parameters as a separate entity and store them in a separate table.
It sounds like your entities are:
The relationships between entities is:
This last relationship will require an additional table to resolve. You can have a separate table that stores your input parameters, and associate the input parameters with a run_id. This table could look like:
run_parameter_id ... run_id_fk ... parameter_keyword ... parameter_value
Where run_id_fk is a foreign key to the appropriate row in the Runs table (described in your question). The parameter_keyword is just used to keep track of the name of the parameter (parameter_1_exp1, parameter_2_exp1, etc).
Your queries to read/write from the database now become a bit more complicated (needing a join), but no longer reliant on creating tables on the fly.
Let me know if this is unclear and I can provide a potential database diagram.