There is a product catalog, i.e. category -> product. The problem is that in different categories, the number of fields in the product may vary. For example, if a product is in category1, it has two fields (name, description), if category2, then the product already has 3 fields (name, short description, full description). At the same time, there is one table for products, let's call its items for example. So how best to organize the structure of the database? I see two options: store all possible fields in the items table or create a table under the products fields, i.e something like this

items_fields

id item_id field_name

But then you need another table for the contents of the fields,

fields

id items_fields_id content

the result is too difficult.

Can someone advise more options?

Thank you all, I decided to stop on the variant with several tables.

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

2 answers 2

Make 4 tables 1 table, this is category with columns id, name. 2 table, this products, with columns id, name, category_id. 3 table, this property, with columns id, name, product_id. 4 table, this is property_values, with columns, id, description, property_id (preferably UNIQUE INDEX should be hung on this column so that id is unique, only 1 value for one property - well, if necessary)

As a result, we have, in one category there can be many products, in 1 product there can be many properties, tobish "Short description" or "Full description" or whatever you want to call other properties, and accordingly the meaning for these properties is "This is a good product." and it will be possible to add, in 1 product at least 10 different properties, and in the other only 2

  • Thank you, figured it out. Now the question is how to display this case, i.e. for example, the html form field, maybe textarea, or just text for example. So far, I am thinking of making a type column in a text type database, and setting a type in it (textarea, text, etc.). - Guest
  • here html? when transferring to a variable such as GET or POST, it is not written there that the data came from textarea or input text))) in the database if you know how many words you have, put VARCHAR (limit number) if you don’t know how many characters Put the type TEXT and everything, at this stage it will be enough. You can display this whole thing through JOINs. in one request get all the data. - JuraZubach
  • Thank you so much, I went to create :) - Guest

As an option to store the maximum possible number of fields in the grocery table. You probably have templates - let them display only those fields that are needed within this template. If you still want to filter the output at the model or controller level, you can associate a table with the categories table that will store the list of properties that should be displayed with this category.