How to set up an SQL table(s) structure for product categories?

Question

I'm pretty new to SQL and I am currently trying to make a simple product page in PHP and MySql. Now I have a products table which has my product id, name, price and stock.

Edit: Sample products tables:

|----------------------------------------------------------------|
|    id    |         name        |       price    |     stock    |
|----------------------------------------------------------------|
|     1    |    Guess T-Shirt    |       10       |      100     |
|----------------------------------------------------------------|

The thing I'm trying to do is having different categories for the products, such that each product can fall into multiple categories.

Here are the options that each product will have:

Now as you see, there are different categories each with sub-categories.

On first thought, I wanted to have a table with all these categories as rows and a foreign key to id from my products table. I realized it would not be the best way to do it, but I also don't know what would be the right way to do it.

I apologize in advance if the question might be irrelevant. I don't have any codes to show, I just need some input on the correct database structure.

If you require more detail, please comment down and I'll update this post. Thanks

Answer 1

You need a product table with this fields

• product_id primary key
• Visibility, probably boolean
• Stock, probably boolean
• Discount, integer
• Remarks, probably boolean

Then a table category for the left side of the menu

• category_id primary key
• name {basic, collection, shirts, trouser, dresses}

and a pivot table product_category

• product_id
• category_id

Finally your query will be

SELECT p.*
FROM products p
INNER JOIN product_category pc
        ON p.product_id = pc.product_id
WHERE 
     p.Visibility = @visibilty  
 AND p.Stock = @Stock
 AND p.Discount = @Discount
 AND p.Remarks = @Remarks
 AND pc.Category_id in (@category);