To control the experimental setup user inputs the following table which defines unique parameter configurations, i.e. combinations of parameter values under which the experiments should be run. The amount of parameters and their meaning can be arbitrary, here, for instance, we've got Mode, AmpGain and InputVoltage.
| ConfigName | Mode | AmpGain | InputVoltage |
|---|---|---|---|
| MX1_LG_HV | MX1 | 1 | 3.3 |
| MX1_HG_HV | MX1 | 10 | 3.3 |
| MX2_LG_HV | MX2 | 1 | 3.3 |
| MX2_HG_HV | MX2 | 10 | 3.3 |
| MX2_LG_LV | MX2 | 1 | 1.8 |
| MX2_HG_LV | MX2 | 10 | 1.8 |
In my application this data needs to be saved in the SQL-like database (e.g. SqLite). From various articles on database design patterns I found out Entity-Attribute-Value Pattern is a possible approach to represent this model, namely:
Parameter table to describe the parameters
| ParamID (PK) | ParamName |
|---|---|
| 1 | Mode |
| 2 | AmpGain |
| 3 | InputVoltage |
Config table to describe the configurations
| ConfigID (PK) | ConfigName |
|---|---|
| 1 | MX1_LG_HV |
| 2 | MX1_HG_HV |
| 3 | MX2_LG_HV |
| 4 | MX2_HG_HV |
| 5 | MX2_LG_LV |
| 6 | MX2_HG_LV |
Finally, the table combining the relationship, where ConfigID and ParamID form a Primary Key:
| ConfigID | ParamID | ValueInt | ValueFloat | ValueStr |
|---|---|---|---|---|
| 1 | 1 | NULL | NULL | MX1 |
| 1 | 2 | 1 | NULL | NULL |
| 1 | 3 | NULL | 3.3 | NULL |
| 2 | 1 | NULL | NULL | MX1 |
| 2 | 2 | 10 | NULL | NULL |
| 2 | 3 | NULL | 3.3 | NULL |
| 3 | 1 | NULL | NULL | MX2 |
| 3 | 2 | 1 | NULL | NULL |
| 3 | 3 | NULL | 3.3 | NULL |
| 4 | 1 | NULL | NULL | MX2 |
| 4 | 2 | 10 | NULL | NULL |
| 4 | 3 | NULL | 3.3 | NULL |
| 5 | 1 | NULL | NULL | MX2 |
| 5 | 2 | 1 | NULL | NULL |
| 5 | 3 | NULL | 1.8 | NULL |
| 6 | 1 | NULL | NULL | MX2 |
| 6 | 2 | 10 | NULL | NULL |
| 6 | 3 | NULL | 1.8 | NULL |
It this the right way to implement the EAV Pattern? Is there any better way to model that data considering there won't be more than 25-30 parameters and this data needs to be accessed/saved only once per minute?
I finally decided to store these parameters serialized into a JSON field, as it has been suggested by @Panagiotis Kanavos. This is far less hassle than implementing EAV pattern, plus, many ORM frameworks support accessing JSON field.