如何为产品类别设置 SQL 表结构? [英] How to set up an SQL table(s) structure for product categories?

问题描述

我对 SQL 还很陌生，我目前正在尝试用 PHP 和 MySql 制作一个简单的产品页面.现在我有一个 products 表，其中包含我的产品 id、name、price 和 stock.

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.

示例 products 表:

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.

首先，我想要一个表，其中包含所有这些类别作为行，以及一个指向 products 表中的 id 的外键.我意识到这不是最好的方法，但我也不知道什么是正确的方法.

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

推荐答案

您需要一个带有此字段的 product 表

You need a product table with this fields

• product_id 主键
• 可见性，可能是布尔值
• 股票，可能是布尔值
• 折扣，整数
• 备注，可能是布尔值

然后是菜单左侧的表category

• category_id 主键
• 名称{基本、系列、衬衫、裤子、连衣裙}

和数据透视表product_category

• product_id
• category_id

最后您的查询将是

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);

这篇关于如何为产品类别设置 SQL 表结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！