Database design for a multi-category product market

Question

I want to build an online store with multiple categories. For example, our first category is electronics (including mobile phones, laptops, etc.) and our second category is apparel (including t-shirts, pants, etc.). Regardless of the category, all products have the same attributes, such as name, brand, price, inventory, etc. Each category has unique attributes, for example the electronics category has resolution, RAM and CPU attributes, and the clothing category includes attributes such as sex, size, gender and color.

---

so:

electronics contine: name, brand, price, inventory, resolution, RAM , CPU

clothing contine: name, brand, price, inventory, sex , size, gender, color

---

I've thought of three structures for my database and I can help you with any other statement. in all structure

The first structure: ___________________________________________________________

tables   | columns
---------------------------------------------------------------------
category : id, name
product  : id, cat_ID, name, brand, price, inventory, OtherAttr

cat_ID have a relation of category.id

otherAttr columns type is json and other attributes store in this column.But since I haven't used jSon columns in the database so far, it worries me a little.

the Second structure: __________________________________________________________

tables          | columns
---------------------------------------------------------------------
category        : id, name
product         : id, cat_ID      , name, brand, price, inventory   
attributesName  : id, name        , description                    // STORE ATTR NAMES
attributesValue : id, attrName_ID , value                          // ALL POSSIBLE VALUES ​​FOREACH ATTR, SUCH AS MAIL AND FEMAIL FOR SEX
categoryAttr    : id, attrName_ID , cat_ID                         // ALL UNIQE ATTR FOR EACH CATEGORY SUCH AS SEX, SIZE, GENDER AND COLOR FOR CLOTHING
productsAttr    : id, product_ID  , attrValue_ID                   

This is a very flexible method but the worrying thing about this structure is that you can't filter products based on a few unique attributes, for example I don't know how to find the perfect query for mobile that has 2GB of RAM and dual core CPU. ?

The third structure ____________________________________________________________

tables      | columns
---------------------------------------------------------------------
category    : id, name
product     : id, cat_ID    , name      , brand, price , inventory
electronics : id, product_ID, resolution, RAM  , CPU
clothing    : id, product_ID, sex       , size , gender, color

and for each other category create a relation table This structure increases the size of the code and takes more time from the programmer, but it is easier for the admins to insert data and provide more filtering capabilities.

Answer 1

I think the second structure is the one you should go with, it needs more improvements but that's not the question.

I don't know how to find the perfect query for mobile that has 2GB of RAM and dual-core CPU?

Here's an example of SQL query to find a mobile that has 2GB of RAM and dual-core CPU:

SELECT *
    FROM product
    WHERE cat_ID = mobile_cat_id_here
      AND EXISTS(
            SELECT 1
            FROM productsAttr
            WHERE productsAttr.product_ID = product.product_ID
              AND attrValue_ID = ram_2g_AttrValue_Id
        )
      AND EXISTS(
            SELECT 1
            FROM productsAttr
            WHERE productsAttr.product_ID = product.product_ID
              AND attrValue_ID = dual_core_AttrValue_Id
        )

mobile_cat_id_here is the category ID of mobile, ram_2g_AttrValue_Id is the ID of the 2G RAM value, and dual_core_AttrValue_Id is the ID of the dual-core CPU value.