If you do something like a questionnaire, which will then filter (search), how best to store information?

age town rost ves cvet_volos razmer_obuvi etc. 22 1 154 45 2 36 ...

Or write somewhere the order of the columns and store everything in a row

 id data 1 22-1-154-45-2-36

Columns will increase over time, that is, the items in the questionnaire will become more and more. Is there any column limit? And on request?

If you use the first option, the query will be:

 ла ла ла WHERE ... AND... AND... AND... AND...

What is the limit here? And even if there is no limit, it will be very heavy requests? Or is MySQL fast and it will not be difficult for it to process a request with 100 conditions?

And in general, how best to do? What other ways are there? Right, so to speak :)

  • Well, firstly, stop interfering with the French Nizhny Novgorod ... - karmadro4
  • one
    Keep it as you wish, and use something like apache lucene to search . - Zowie
  • 2
    And why is there AND...AND...AND ? Are you going to select rows by the totality of all columns? Strange occupation. If one row is needed, there is a surrogate key id for this. And if not one - how do you imagine an analogue of WHERE town = 1 AND cvet_volos = 2 for the format '22-1-154-45-2-36' ? LIKE '%-1-%-%-2-%' very unfortunate idea. And WHERE town = 1 AND ves > 120 is generally a dead number. Indices to help. - drdaeman
  • one
    And instead of age, birthday is better. After a while there will be less hassle. - avp
  • one
    If there is a choice - there are more document-oriented databases that look like CouchDB (well, or MongoDB, if the data is to be released), rather than relational ones. - drdaeman

2 answers 2

There is a way with key -> value, but it is not very convenient for searching.

Your first option is the most convenient to search. Queries are quite ordinary. And indexing by priority columns can always be done.

The second is more flexible in terms of adding columns, but a targeted search for any column will be difficult.

  • one
    Flexibly ?! Simply estimate the amount of work to add the dlina_uschej attribute before the razmer_obuvi attribute. - karmadro4
  • @ karmadro4, I always need only - after. :) - Angelina_Jo
  • For the second option, it is better to serialize the data and write it into a text field. But there will be problems with the search, with any data recording format. - Fucking Babai

There is another option to store in the form of a table of property values. Those. For each form, n records are created in the property value table with the fields anketa_id, property_id, property_value .

This method of storage is far from optimal in terms of speed of access to data (compared to a regular table) and ease of making changes, as well as heterogeneous data (in particular, different types), but it allows you to change (add) the composition of these properties on the fly without changing the structure of the database .

This method of storage is used, for example, in Bitrix for the properties of goods. In 1C, they also like all sorts of different properties, moreover, with a composite type.

To work as a flat table, a simple query is built from the first item by a loop on the property table:

 select anketa.id, pv1.property_value age, pv2.property_value town ... from anketa left join prop_values pv1 on anketa.id=pv1.anketa_id and pv1.property_id=1 left join prop_values pv2 on anketa.id=pv2.anketa_id and pv2.property_id=2 ...

for this query, you can create an appropriate view and change it when adding new properties. indices depending on the needs here may need several different (including composite).

The option is not without flaws, but for certain tasks such as yours, IMHO, is acceptable.