The task is this: there is an online shoe store, one shoe model exists in several sizes. How best to organize the storage of information about these balances in MySQL databases? Give an idea!

  • But why should they be stored somewhere? the rest is goods less than X. i.e. it is a normal select with a condition, you can make a display (view), as a last resort. - Alex Kapustin
  • They need to be stored in order to prevent the user from ordering goods that are not in stock. - Indifferent

3 answers 3

If the number of different sizes is small, then you can make one record for each model and store the remnants of different sizes in the fields of this record. And even if some individual models have an extended set of sizes, then for such models you can add a second (dummy) entry. She will not be filled with the name of the model, or any other fields that describe a separate model. There will be only an ID stored in one of the fields of the main record, and the remnants of additional sizes. In this case, the minimum size from the additional record should be interpreted as the size following the maximum of the main one.

  • An interesting option, but it gives an extra load on the muscle server. I would not use it myself and would not recommend it to others ... - Indifferent
  • I myself would not do that either. But, as I understand it, you want a strange one. He offered the strangest thing he could think of. And if in a good way, then it should be done either in two tables (as written above), or in one - by a separate record for each size. In the second case, the information describing the model itself can be stored in any size. For example, in the one that goes to the warehouse and is added to the database first. To get a list of models, you need to select records, where the model name is NOT NULL. - Shamov
  • I do not want anything strange ...) I'm just trying to transform a shop window into an online store with the least cost of characters ...) - Indifferent
  • Then you just need to choose from two banal options. The first is more canonical. It corresponds to the 3rd normal form. And the variant with one table does not correspond to it, but it is simpler. - Shamov
  • At the expense of simplicity I do not agree, a funny “crutch” is described rather. But anyway, thanks for the idea - any ideas help to find the perfect solution. - Indifferent

Separate table, where model ID, size and balance are stored.

  • This idea came to my mind first. But I do not like requests with which help this information should be updated. No more ideas? - Indifferent
  • And what you do not like the request? the first query makes a query to the main table and displays information about the model, and the second query based on the ID receives from an additional table what sizes are available. - vdk company
  • Those. nothing better to come up with? ( - Indifferent
  • And what's the point of creating another table with, in fact, dubbing information? No need to produce tables. @savro is right, a request for product id and comparison of the result with 0 Besides, an extra table, if you make a direct request from the code, and not through any API, will only complicate the perception: "why is there another table? What did the author have in mind when he created it and are there any unobvious motives? ” - Ekkertan
  • Meaning in a separate table is - this is not discussed. In addition, there is no duplication. And the perception of other coders is a problem of other coders ...) - Indifferent

As an option, add 3 tables: table_properties, table_values ​​of properties, table_connection of the value with the goods.

And then calmly fill properties with values ​​(for example, color {red blue green}, size {41, 42, 43}, etc.).

Tying several properties to the product, we get the product with several modifications.

Plus - you do not need to add fields in the tables for new properties of the product, you do not need to produce records with the goods.

Minus - complex communication logic.

PS is the implementation of this logic on heavily loaded projects. The filter sampling rate has a positive effect.