title: Database design for a multi-category product market
body: <p>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.</p>
  
  <hr>
  
  <p>so:</p>
  
  <p>electronics contine: name, brand, price, inventory, resolution, RAM , CPU</p>
  
  <p>clothing    contine: name, brand, price, inventory, sex       , size, gender, color</p>
  
  <hr>
  
  <p>I've thought of three structures for my database and I can help you with any other statement.
  in all structure</p>
  
  <p>The first structure: ___________________________________________________________</p>
  
  <pre><code>tables   | columns
  ---------------------------------------------------------------------
  category : id, name
  product  : id, cat_ID, name, brand, price, inventory, OtherAttr
  </code></pre>
  
  <p>cat_ID have a relation of category.id</p>
  
  <p>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.</p>
  
  <p>the Second structure: __________________________________________________________</p>
  
  <pre><code>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                   
  </code></pre>
  
  <p>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. ?</p>
  
  <p>The third structure ____________________________________________________________</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>