数据库设计问题-不同类别的多个分数
假设我有一个服装产品数据库。 除了许多其他字段之外，数据库还需要存储每个产品不同类别的评分（满分 10 分）。
例如，一个类别可以是“场合”。 场合可能如下：
办公室穿着 参加婚礼时穿 约会时穿 ... 参加葬礼时的穿着
每个产品都需要被分配一个分数（满分 10 分）。 因此，一种产品可以具有：
办公室：5 婚礼：7 日期：10 葬礼：0
这表明该物品是令人愉快的，不太低调，也许不太正式。
有很多这样的类别，这些类别将用作搜索算法的一部分，因此速度可能是一个问题。 另外，我不希望我的产品表变得很大。 这意味着我对将其存储在产品表中感到不安，因为每个响应都有自己的列。
可能以这种方式存储，除非在带有连接的不同表中？ 只是寻找一个相当灵活和优雅的解决方案。
如果我没有说清楚，请告诉我。
PS 我正在使用 MySQL，如果这有什么不同的话...
编辑：我不认为拥有一个类别表和一个带有 Product_id、category_id 和 Score 的连接表会很好地工作。 您要么必须将连接中的每个分数列（例如 wedding_score 等）命名为别名，要么返回多个分数列（每个类别一个）。
Say I have a database of clothing products. In addition to many other fields, the database needs to store ratings (marks out of 10) on different categories for each product.
For example, one category could be 'occasions'. The occasions could be as follows:
Wear to the office
Wear to a wedding
Wear on a date
...
Wear to a funeral
Each product needs to be assigned a score out of 10 for each of these. So one product could have:
Office: 5
Wedding: 7
Date: 10
Funeral: 0
Which would indicate the item is something cheerful, not too understated, not too formal perhaps.
There are a number of categories like this, and these will be used as part of a search algorithm so speed could potentially be an issue. Also I don't want my product table to get huge. This means I'm uneasy about storing this in the product table as each response having it's own column.
Possibly store this way except in a different table with a join? Just looking for a reasonably flexible and elegant solution.
If I haven't been clear please let me know.
P.S Am using MySQL if that makes a difference...
EDIT: I don't think having a category table and a join table with product_id, category_id and score would work very well. You would either have to name each score column (e.g. wedding_score etc) in the join as aliases, or get multiple score columns returned (one for each category).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为这是正确的方法，因为它可以让你以相同的方式对待不同的分数。 添加、重命名、删除等分数类型时，您的大部分代码不需要调整。
如果您担心聚合此信息的复杂连接，也许一两个视图会有所帮助。
I think this IS the way to go, because it lets you treat the different scores identically. Most of your code won't need to be adjusted when a score type gets added, renamed, removed, etc.
If you're worried about complicated joins for aggregating this info, maybe a view or two would help.
你走在正确的轨道上。 有一个“类别”表，中间表有两列，一列是产品的 PK，另一列是产品的 PK； 再加上三分之一的分数。 相当标准的图案。 然后，典型地，中间表将具有复合PK（例如productid+categoryid）和categoryid上的另一个非唯一索引。
You're on the right track. Have a table for "category", with an intermediate table with two columns, one the PK for products, the other the PK for the product; plus a third for the score. Pretty standard pattern. Then typically the intermediate table will have a composite PK (e.g. productid + categoryid) and another non-unique index on categoryid.
好吧，关系数据库针对存储关系进行了优化，因此一个用于存储项目描述的表、另一个用于存储评分类别（包括分数描述）的表和一个连接表（要评分的项目）似乎是完美的答案。
有什么缺点？ 嗯，每个项目都会有几个分数记录，没错。 它在查询浏览器中也不是那么可读。 但有了正确的键和索引，它就足够快，可以按照您需要的方式使用它。
Well, A relational database is optimised for storing relationships so a table for storing item descriptions, another for Scoring Categories (including Score Description) and a joining table (item to score) seems the perfect answer.
What are the drawbacks? Well, each item will have several score records, thats true. It also wouldn't be that readible in a query browser. But with the correct keys and indexes it would be fast enough to use the way you need it.