动态类别的数据库架构 [英] DB Schema for Dynamic Categories

问题描述

添加了更新#1 .请在问题末尾检查.谢谢.

Added Update #1. Please check at the end of the question. Thanks.

朋友

我正在设计一个带有类别和与类别相关联的过滤器的产品清单.我目前有一个带有静态类别的数据库架构.我必须让它们充满活力.我找不到合适的架构来使其动态化，因此我在表单中对架构进行了硬编码.我的详细工作如下.

I am designing a Product Listing with categories and filters tied to the categories. I currently have a database schema with static categories. I have to make them dynamic. I couldn't find out the right schema to make it dynamic, so I hard code the schema in the form. My detailed work is as follows.

主要产品表是所有产品的索引，并且每个产品类别都有单独的字段.例如，考虑一辆自行车和一台电视机.这两种产品共享产品表中的公共字段:

The main products table is the index of all the products and each category of products have separate fields. For eg., consider a bike and a TV. These two products share common fields that are in the product table:

1. 产品ID
2. 名称
3. 价格
4. 照片
5. 类别

当涉及到类别时，它具有一些额外的字段.因此，自行车属于汽车类别，其中包含以下字段:

And when it comes to the category, it has some extra fields. So, bike comes in the automobiles category, where it has fields:

1. 里程
2. 引擎类型
3. 座圈
4. 燃油

对于TV产品，它属于其自己的类别TV，其字段为:

For the TV product, it comes in its own category, TV, where the fields are:

1. 尺寸
2. 视频类型
3. 响应时间
4. 输入选项

当前我的数据库结构如下:

Currently my database structure is as follows:

DESC `Products`;
+------------+--------------+------+-----+---------+----------------+
|      FIELD |         TYPE | NULL | KEY | DEFAULT |          EXTRA |
+------------+--------------+------+-----+---------+----------------+
| Product ID |      int(11) |   NO | PRI |  (null) | auto_increment |
|       Name | varchar(255) |  YES |     |  (null) |                |
|      Price |      int(11) |  YES |     |  (null) |                |
|      Photo | varchar(255) |  YES |     |  (null) |                |
|   Category |      int(11) |  YES |     |  (null) |                |
+------------+--------------+------+-----+---------+----------------+

类别类别为:

DESC `television`;
+---------------+--------------+------+-----+---------+-------+
|         FIELD |         TYPE | NULL | KEY | DEFAULT | EXTRA |
+---------------+--------------+------+-----+---------+-------+
|    Product ID |      int(11) |  YES |     |  (null) |       |
|    Dimensions |  varchar(25) |  YES |     |  (null) |       |
|    Video Type | varchar(255) |  YES |     |  (null) |       |
| Response Time |  varchar(25) |  YES |     |  (null) |       |
| Input Options |         text |  YES |     |  (null) |       |
+---------------+--------------+------+-----+---------+-------+

DESC `automobiles`;
+-------------+--------------+------+-----+---------+-------+
|       FIELD |         TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------------+--------------+------+-----+---------+-------+
|  Product ID |      int(11) |  YES |     |  (null) |       |
|     Mileage |      int(11) |  YES |     |  (null) |       |
| Engine Type | varchar(255) |  YES |     |  (null) |       |
|      Seater |      int(11) |  YES |     |  (null) |       |
|        Fuel | varchar(255) |  YES |     |  (null) |       |
+-------------+--------------+------+-----+---------+-------+

当我将数据插入数据库时​​，我使用这种方式:

And when I insert data to the database, I use this way:

INSERT INTO `television`
    (`Product ID`, `Dimensions`, `Video Type`, `Response Time`, `Input Options`)
VALUES
    (1, 100, 'hd', 2, 'hd');
INSERT INTO `automobiles`
    (`Product ID`, `Mileage`, `Engine Type`, `Seater`, `Fuel`)
VALUES
    (1, 100, 'hd', 2, 'hd');

---

HTML/PHP

对于一组静态类别来说似乎不错，在这里我使用如下所示的简单形式.

---

HTML / PHP

Seems to be fine for a static set of categories, where I use a simple form like the following.

电视

<form action="new.php">
    <ul>
        <li>
            <label>Name</label>
            <input type="text" />
        </li>
        <li>
            <label>Price</label>
            <input type="text" />
        </li>
        <li>
            <label>Photo</label>
            <input type="text" />
        </li>
        <li>
            <label>Category</label>
            <input type="text" />
        </li>
        <li>
            <label>Dimensions</label>
            <input type="text" />
        </li>
        <li>
            <label>Video Type</label>
            <input type="text" />
        </li>
        <li>
            <label>Response Time</label>
            <input type="text" />
        </li>
        <li>
            <label>Input Options</label>
            <input type="text" />
        </li>
        <li>
            <input type="submit" />
        </li>
    </ul>
</form>

汽车

<form action="new.php">
    <ul>
        <li>
            <label>Name</label>
            <input type="text" />
        </li>
        <li>
            <label>Price</label>
            <input type="text" />
        </li>
        <li>
            <label>Photo</label>
            <input type="text" />
        </li>
        <li>
            <label>Category</label>
            <input type="text" />
        </li>
        <li>
            <label>Mileage</label>
            <input type="text" />
        </li>
        <li>
            <label>Engine Type</label>
            <input type="text" />
        </li>
        <li>
            <label>Seater</label>
            <input type="text" />
        </li>
        <li>
            <label>Fuel</label>
            <input type="text" />
        </li>
        <li>
            <input type="submit" />
        </li>
    </ul>
</form>

---

我的问题

现在表单数据很简单.我只是使用表的结构来插入数据库.我现在面临的问题是，如果类别应该是动态的，该怎么办?我应该如何更改数据库架构以使其动态?

---

My Problem

Now the form data is simple. I just use the structure of table to insert into the database. The problem now I face is, what if the categories are supposed to be dynamic? How am I supposed to change my database schema to make it dynamic?

好的，让我定义一下我对动态的看法.当管理员想要添加新类别时，我目前要做的是，为类别创建新表单，转到phpMyAdmin并添加新表，然后再次添加相同的插入查询和新文件.我想通过管理面板单独使用PHP来执行此操作.这可行吗?我应该进行哪种模式更改?

Okay, let me define what's my view of dynamic. When the admin wants to add new categories, what I currently do is, create new forms for the categories, go to phpMyAdmin and add a new table and again the same insert queries and new files will be added. I want to do this via an admin panel using PHP alone. Is this feasible? What kind of schema changes should I make?

首先.谢谢！现在，我以这种方式即兴创作了桌子.现在我有四个桌子.

First of all. Thanks! Now I have improvised my table this way. Now I have four tables.

1. 产品-所有产品的索引.
2. 类别-要使用名称吗?包括类别名称及其父类别.
3. 属性-包括类别的属性名称.
4. 关联-Product ID，Attribute ID及其值的关联.

1. Products - Index of all the products.
2. Categories - For name-sake? Includes the category name and its parent.
3. Attributes - Includes the attribute names for the category.
4. Association - The association of the Product ID, Attribute ID, and its value.

现在，我正在努力.这看起来很有希望.将等待答案，同时也更新我的工作！ :)谢谢.

Now, well, I am working on. This looks promising. Will wait for answers, at the same time, update my work too! :) Thanks.

推荐答案

我认为您需要更改数据库架构.在这里，我可以看到类别是第n级其他父类别的子类别.子类别也具有不同的值字段.为此，您可能需要自定义字段管理，以使这些字段也变得动态.因此，我的建议模式如下所示.

I think you need to change your database schema. Here I can see that the categories are child to other parent categories with nth level. And also sub categories have different value field. For this you may required custom field management, to make those field also dynamic. So altogether my suggestive schema will be as follows.

产品

Product_ID, Name, Price, Photo, Category_ID (forignkey of category table)

类别

Category_ID, Name, Parent_ID

Category_Field

Category_Field

Category_Field_ID, Category_ID, Name, Type

Product_Category_Field_Value

Product_Category_Field_Value

ID, Product_ID, Category_ID, Category_Field_ID, Value

因此，在产品表中，我们通过Category_ID映射了类别.在类别表中，我们通过parent_ID维护子类别.这意味着，如果类别是根类别，则在parent_ID中使用0，否则在其父类别中使用Category_ID.在Category_Field中，使用与类别关联的自定义字段名称.这里的type是字段或数据的类型，例如可以在type中使用的文本，textarea，select，radio等. Product_Category_Field_Value用于每个产品的自定义字段的值.使用它会起作用.一切顺利.

So here in Product table we mapped category by Category_ID. In Category table we maintain sub category by parent_ID. It means if the category is a root category use 0 in parent_ID else use Category_ID of its parent category. In Category_Field use custom field name which associate with category. Here type is the type of field or data, for example text, textarea, select, radio etc you can use in type. Product_Category_Field_Value is used for value of custom field for each product. Use this it will works. All the best.

这篇关于动态类别的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！