There was a task to write a lot of different parameters (options) that are generated using select , text-field and many check-button , radio-button .

What is the best way to store them in a SQLite database?

Maybe we should take one column in the table, name it param and write data to it in JSON format? Or how best to store such data?

  • one
    and what will you do with this data? - zRrr

3 answers 3

Create a column and store Json. The main thing that would not search it

If you do a search, then structurally divide them into groups.

  • The only question that remains is how to save half the parameters in json in rails? - Pavel

With SQLite, you have almost no options. Only serialization or storage by separate table.

The first is implemented in one line :

 serialize :fields, JSON

... and this, in fact, you will lose the ability to make requests with conditions on these fields.

If you need to search for these fields, you will have to make them a separate model and table and make requests to them of this type:

 Thing.joins(:fields).merge( Field.where(key: 'foo', value: 1..5))

Depending on the need to store different types of values ​​there, the conditions of the selection can be complicated by type conversions in SQL.

But I note that you do not want to use SQLite in a web application that will be used simultaneously by more than one person.

    If you use Postgres, then it has an extension hstore and under it is written gem . This extension allows you to store in the field a set of ключ-значение pairs (both of which are a string), and to select by the content of such fields.

    PS

    Hurried, not read. You wrote that you have SQLite. This is just one of the downsides of using Hstore.

    Another disadvantage is the need to install this extension each time, even to run in development mode. But you can get away from this using a docker or similar solution.

    • the need for a new machine to install this extension each time - migrations automate it, no? - D-side
    • @ D-side Yes, if the necessary libraries are installed that need to be installed - askubuntu.com/questions/354686/… . - MAXOPKA
    • And for pg heme they need to be set. This is addressed more to the questioner, they say, a minus is quite cotton. And also, from 9.4 there is JSONB! Unlike hstore, it can arbitrary nesting, but fewer supported types. - D-side