Data storage structure in MySql



  • There is a list of products with a number of characteristics. The goods have general characteristics: e.g. weight and size. There are unique characteristics of some products, such as colour (in addition to the first two). The different unique characteristics of different products may be up to 100 and the purpose is to arrange storage in such a way that the value of a characteristic can work in WHERE.

    I plan to place each of the possible characteristics in a separate column, so the question is: how much is the productivity of the OBD, depending on the number of columns, if the columns reach 100? Can there be a better way to store and process data in this situation?



  • Keeping a lot of parameters can be done in two ways, as you suggested - a parameter - a column. And in the form of a separate table, where for each of the records of the basic table there are many records in which the id of the basic record, the id type of parameter and the meaning of this parameter.

    Both approaches have their pros and cons. When the parameters are separate and separate lines, but the column with the parameter value is only one, the index can be built and the id of the required parameter records can be found at the time. On the other hand, when it is necessary to simply show all the parameters to the particular product, the sample of each parameter shall be stopped by a separate line. If the parameters of the 100, it's pretty confusing.

    In your approach, a sample from the id product table will be instant, all parameters are on the same record, so for one disc operation we get everything we need to know about the product. That's great. DO For 100 columns, indexes cannot be constructed because each additional index occupies a lot of space on the disk and slows the entry of new records, because each index should be partially restructured when entering the record. Once we leave columns with no index values, any search on them will have to read the whole table from the disc. In addition, when adding a new parameter, a new column should be added, clearly indicated in samples and possibly referred to in different parts of the code.

    There's a third approach. Store attributes and columns and records in a separate table. A separate table shall be used to trace the parameters. And when it comes to a particular product, the data comes directly from the basic record. Yes, this approach overstates data. If any parameter changes, it needs to be changed in two places. In principle, this can be done by triggers for reliability. Changes will be a little longer than only one table.

    Totally. The truth is there.We need to find a balance. Some attributes may be held in two locations, and some secondary parameters may be retained only in the search table but not in the main.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2