分类网站的数据库结构
我正在开发一个类似于 Quickr.com 的分类网站。
主要问题是每个类别需要一组不同的属性。例如，对于手机，属性可能是制造商、操作系统、是否触摸屏、是否启用 3G 等。而对于公寓，属性可能是卧室数量、家具、楼层、总面积等。每个类别的属性和属性数量各不相同，我将属性及其值保留在单独的表中。
我当前的数据库结构是
表classifieds_ads，
这个表存储了所有的广告。每个广告一条记录。
ad_id
广告标题
广告描述
广告创建时间
cat_id
示例数据
-----------------------------------------------------------------------------------------------
|ad_id | ad_title | ad_desc | ad_created_on | cat_id |
-----------------------------------------------------------------------------------------------
|1 | Nokia Phone | Nokia n97 phone for sale. Excellent condition | <timestamp> | 2 |
-----------------------------------------------------------------------------------------------
表classifieds_cat
该表存储所有可用的类别。 classifieds_ads 表中的 cat_id 与该表中的 cat_id 相关。
cat_id
类别
parent_cid
示例数据
-------------------------------------------
|cat_id| category | parent_cid |
-------------------------------------------
|1 | Electronics | NULL |
|2 | Mobile Phone | 1 |
|3 | Apartments | NULL |
|4 | Apartments - Sale | 3 |
-------------------------------------------
表classifieds_attribute
此表包含特定类别的所有可用属性。与classifieds_cat表相关。
attr_id
猫_id
输入类型
attr_label
attr_name
示例数据
-----------------------------------------------------------
|attr_id | cat_id | attr_label | attr_name |
-----------------------------------------------------------
|1 | 2 | Operating System | Operating_System |
|2 | 2 | Is Touch Screen | Touch_Screen |
|3 | 2 | Manufacturer | Manufacturer |
|4 | 3 | Bedrooms | Bedrooms |
|5 | 3 | Total Area | Area |
|6 | 3 | Posted By | Posted_By |
-----------------------------------------------------------
表classifieds_attr_value
此表存储classifieds_ads 表中每个广告的属性值。
attr_val_id 属性ID 广告ID attr_val
示例数据
---------------------------------------------
|attr_val_id | attr_id | ad_id | attr_val |
---------------------------------------------
|1 | 1 | 1 | Symbian OS |
|2 | 2 | 1 | 1 |
|3 | 3 | 1 | Nokia |
---------------------------------------------
========
- 这个设计可以吗？
- 是否可以使用 solr 索引这些数据？
- 如何对此数据执行分面搜索？
- MySQL 是否支持像 solr 一样的字段折叠？
I am developing a classifieds website similar to Quickr.com.
The main problem is that each category requires a different set of properties. For example, for a mobile phone the attributes might be Manufacturer, Operating System, Is Touch Screen, Is 3G enabled etc... Whereas for an apartment the attributes are Number of bedrooms, Is furnished, Which floor, total area etc. Since the attributes and the number of attributes varies for each category, I am keeping the attributes and their values in separate tables.
My current database structure is
Table classifieds_ads
This table stores all the ads. One record per ad.
ad_id
ad_title
ad_desc
ad_created_on
cat_id
Sample data
-----------------------------------------------------------------------------------------------
|ad_id | ad_title | ad_desc | ad_created_on | cat_id |
-----------------------------------------------------------------------------------------------
|1 | Nokia Phone | Nokia n97 phone for sale. Excellent condition | <timestamp> | 2 |
-----------------------------------------------------------------------------------------------
Table classifieds_cat
This table stores all the available category. cat_id in classifieds_ads table relates to cat_id in this table.
cat_id
category
parent_cid
Sample data
-------------------------------------------
|cat_id| category | parent_cid |
-------------------------------------------
|1 | Electronics | NULL |
|2 | Mobile Phone | 1 |
|3 | Apartments | NULL |
|4 | Apartments - Sale | 3 |
-------------------------------------------
Table classifieds_attribute
This table contains all the available attributes for a particular category. Relates to classifieds_cat table.
attr_id
cat_id
input_type
attr_label
attr_name
Sample data
-----------------------------------------------------------
|attr_id | cat_id | attr_label | attr_name |
-----------------------------------------------------------
|1 | 2 | Operating System | Operating_System |
|2 | 2 | Is Touch Screen | Touch_Screen |
|3 | 2 | Manufacturer | Manufacturer |
|4 | 3 | Bedrooms | Bedrooms |
|5 | 3 | Total Area | Area |
|6 | 3 | Posted By | Posted_By |
-----------------------------------------------------------
Table classifieds_attr_value
This table stores the attribute value for each ad in classifieds_ads table.
attr_val_id
attr_id
ad_id
attr_val
Sample data
---------------------------------------------
|attr_val_id | attr_id | ad_id | attr_val |
---------------------------------------------
|1 | 1 | 1 | Symbian OS |
|2 | 2 | 1 | 1 |
|3 | 3 | 1 | Nokia |
---------------------------------------------
========
- Is this design okay?
- Is it possible to index this data with solr?
- How can I perform a faceted search on this data?
- Does MySQL support field collapsing like solr?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我的建议是从
classifieds_attribute表中删除cat_id，然后创建一个新表。新表如下所示：
这应该可以帮助您减少冗余。
My suggestion is to remove
cat_idfrom theclassifieds_attributetable, then create a new table.The new table would look like:
This should help you decrease redundancy.
你的设计很好，尽管我质疑你为什么使用分层类别。我了解您希望从最终用户的角度来组织类别。层次结构可以帮助他们深入了解他们正在寻找的类别。但是，您的架构允许每个级别的属性值。我建议您只需要（或可能需要）叶级别的属性。
当然，您可以想出适用于更高级别的属性，但这将使您的数据管理变得非常复杂，因为您必须花费大量时间来思考某个特定链的具体位置有多高。属性所属以及是否存在某种原因导致较低级别可能成为父规则的例外，等等。
它肯定也会使你的检索变得过于复杂——我认为这是你提出问题的部分原因。
我建议创建一个附加表，用于管理叶级别以上的类别层次结构。它看起来与您的
classifieds_cat表一模一样，只是卷入关系显然与新表有关。然后，classifieds_cat.parent_cid成为新表的 FK，而不是classifieds_cat的内卷 FK。我认为这种架构更改将降低您的应用程序和数据管理的复杂性。
Your design is fine, although I question why you are using hierarchical categories. I understand that you want to organize categories from an end-user standpoint. The hierarchy helps them drill down to the category that they are looking for. However, your schema allows for attribute values at every level. I would suggest that you only need (or possibly want) attributes at the leaf level.
It is certainly possible that you could come up with attributes that would be applicable at higher levels, but this would drastically complicate your management of the data since you'd have to spend a lot of time thinking about exactly how high up the chain a certain attribute belongs and whether or not there is some reason why a lower level might be an exception to the parent rule and so forth.
It also certainly over complicates your retrieveal as well - which is part of the reason for your question, I think.
I would suggest creating an additional table that will be used to manage the hierarchy of categories above the leaf level. It would look exactly like your
classifieds_cattable except the involuted relationship will obviously be to the new table. Thenclassifieds_cat.parent_cidbecomes an FK to the new table rather than an involuted FK toclassifieds_cat.I think this schema change will reduce your application and data management complexity.