0

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.

Poplong
  • 195
  • 1
  • 1
  • 7

1 Answers1

0

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:

  • experiment
  • runs of an experiment, which consist of a response and one or more:
    • input parameters

The relationships between entities is:

  • One experiment to zero or more runs
  • One run to one or more input parameter values (one to many)

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.

deceze
  • 510,633
  • 85
  • 743
  • 889
Michael
  • 425
  • 2
  • 9
  • Thank you very much for your prompt reply. I have been searching around and found similar solutions called entity-attribute-value. This will create a "long and skinny" table as you described and the queries will become a little complicated. Another possible solution is to store the parameter configurations as a BLOB which is a binary object, however, this will not support queries, some additional code will be needed for iterate through the binary object. – Poplong May 16 '16 at 19:30
  • Another option is to store the Parameters in a string, in the form of a JSON object. You can use JSON libraries to help parse through the object, which might be easier than direct BLOB parsing, but like you mentioned would still require additional code. – Michael May 16 '16 at 20:00