分类广告网站的数据库结构 [英] Database structure for a classifieds website
问题描述
我正在开发类似于Quickr.com的分类广告网站。
I am developing a classifieds website similar to Quickr.com.
主要问题是每个类别都需要一组不同的属性。例如，对于手机，属性可能是制造商，操作系统，触摸屏，是否启用3G等等。而对于公寓而言，属性是卧室数量，已装配，哪个楼层，总面积等。自从属性和属性数量因每个类别而异，我将属性及其值保存在单独的表中。
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.
我目前的数据库结构是
此表存储所有广告。每个广告一个记录。
This table stores all the ads. One record per ad.
ad_id
ad_title
ad_desc
ad_created_on
cat_id
ad_id
ad_title
ad_desc
ad_created_on
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
此表存储所有可用类别。分类数据表中的cat_id与此表中的cat_id相关。
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
类别
parent_cid
cat_id
category
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表。
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
attr_id
cat_id
input_type
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表格中的每个广告的属性值。
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
attr_val_id attr_id ad_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吗？
- 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?
推荐答案
您的设计很好，虽然我怀疑你为什么使用分层类别。我明白你想从最终用户的角度来组织类别。层次结构帮助他们深入研究他们正在寻找的类别。但是，您的模式允许在每个级别的属性值。我建议你只需要（或可能想要）属性在叶级别。
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.
我建议创建一个额外的表，用于管理叶级别上方的类别层次结构。它将看起来像您的 classifieds_cat 表，除了渐开线的关系显然将是新的表。然后， classifieds_cat.parent_cid 成为新表的FK，而不是渐进的FK到 classifieds_cat 。
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_cat table except the involuted relationship will obviously be to the new table. Then classifieds_cat.parent_cid becomes an FK to the new table rather than an involuted FK to classifieds_cat.
我认为这种架构更改会降低您的应用程序和数据管理的复杂性。
I think this schema change will reduce your application and data management complexity.
这篇关于分类广告网站的数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
