How to structure a database?

Question

SQL

It is necessary to design the structure of the database.
There are some product categories. For example, the category "shoes". It can be divided by sex "male" and "female", by color and so on. The user of the program that will manage this database must have the following capabilities:

1. View all products of the selected category with options. For example, “shoes”, only for men, are blue;
2. Add a new category to the database;
3. Add a new product;

I see it like this:
One table contains a list of categories. ID, name and string. The string lists the possible options. For example: “gender = male/female; color = green/blue/red". Other tables are created for each category and contain the actual parameters of a particular product. The program loads the possible variants of each category parameter from the first table, and then looks for the necessary elements from the corresponding one.

Will this option work for small loads? If not, please tell me the best way to do it.

Answer 1

I don't think the parameters should be stored in a string. It will be difficult to select products by parameters.
I propose to move the parameters to a separate table, which will contain the fields id, product id, parameter name

Answer 2

Category table: id, name, everything that belongs directly to the category
Product table: product id, other, related to the product, but not the characteristics of the product
Feature table: id, name, description, etc.
Relationship table: product_id, characteristic_id (composite PK), characteristic value
It seems that I haven't forgotten anything.

Answer 3

Read about database normal forms (for example, the topic is covered in Robert Sheldon's book on MySQL). One of the normalization requirements is that the values ​​in the columns must be atomic (that is, no more than one value in a column). In addition, the values ​​should depend only on the primary key (with a high probability this is the row ID in the selected table) and not depend on each other (for example, the sign of the zodiac depends on the month of the year). In general, read. In the same book, a recommendation for designing a database is given.

Answer 4

Категории:
id название и т.п.
Характеристики
id | название | тип (чекбокс, список, текст, строка и т.п.) | варианты выбора, если есть, в каком-нить формате (json, к примеру)
Пример:
1 | Пол | list | [ «женский», «мужской» ]
2 | Цвет | list | [ «синий», «красный», «зеленый» ]
Это нужно для управления выводом формы редактирования/добавления товара какой-либо категории. В итоге у вас будет один и тот же код везде и не нужно заводить таблицу под каждую категорию отдельно. В данном случае ясно, что тип поля list, значит, это select либо группа радиобаттонов, к примеру. А варианты выбора распаковываются из json.
Товары
id | id категории | название…
As I understand it, your product falls into one category, which means you can do one-to-many.
Bundles:
id characteristics | category id
This is to make it clear which fields for editing a product are available in a category, and so that you can link a characteristic to several categories.
product id | feature id | the value of the characteristic
Well, this is understandable.
Thus, when adding a category, the user can add the necessary characteristics there (or choose from existing ones) and save. Well, when adding a product to this category, a beautiful form will be displayed where you can fill in the values ​​of all characteristics.

Answer 5

Feature Type : Feature_Type_ID, Name ( Gender, Color, ... )
Feature value : Feature_Value_ID, Feature_Type ID, Feature Name ( Gender: Male, Gender: Female,... )
Feature Set : Feature_Set_ID, Name ( Blue Male, Blue Female, . .. )
Features set composition : Features_Set_ID, Features_Values_ID ( Blue male / Blue, Blue male / Male, ... )
Product : Item_ID,
Features_Set_ID ).