设计相关产品表 [英] Designing related products table

问题描述

我有一项任务是在现有数据库中实现相关产品功能。由于我是SQL Server新手，不知道如何完成此任务，我想知道是否有一个解决方案，如添加另一列到现有数据库 - RelatedProductID。我们的产品表是这样的：

I am given a task to implement "related products" functionality in an existing database. Since I am a SQL Server novice and not sure how to complete this task I was wondering if there is a solution like adding another column to existing database - RelatedProductID. Our products table is like this:

ProductID int, PK
Name nvarchar(100)
Price decimal
CoverImageURL nvarchar(400)
-- there are couple more columns

推荐答案

这个问题我的想法是它做了两件事：

1）它限制了你可以拥有的一个项目的相关产品的数量 - 如果你看看像亚马逊这样的地方，那通常不是案件。如果你买手机，你可能需要一个封面和一个免提工具包。相反，免提工具包可能适用于十几部手机。

2）移除产品很麻烦，因为你必须检查其他所有产品，看它是否是相关的产品。还有。



我会考虑两种方法：一个单独的表来保存产品和它的相关项目之间的链接，或者一个关系组表一个小组会说六个相关的项目。这取决于你谈论的项目数量，以及你将涉及多少关系。

The problem with that idea is that it does two things:
1) it restricts the number of "related products" you can have to a single item - if you look at somewhere like Amazon, that isn''t often the case. If you buy a phone, you probably want a cover and a hands free kit for example. Conversely, a handsfree kit may apply to a dozen phones.
2) It makes it a pain to remove a product, because you have to check every other product to see if it is a related product of that as well.

There are two approaches I would consider: a separate table to hold a link between product and it''s related item, or a relation group table where a group would hold say six related items. It depends how many items you are talking about, and how many relations you will be involving.

是的，有一个像你提到的解决方案，但它只会在只有一个您的每个产品的相关产品。

但是，如果不是这种情况，您需要引用包含产品之间所有关系的表格。



你可以尝试添加一个新表：

Yes, there is a solution like you mentioned but it’ll only work if there is exactly one related product for each of your products.
However, if this is not the case you’ll need referencing table containing all relations between products.

You can try adding a new table:

CREATE TABLE dbo.ProductsRelation
(
    FirstProductId int not null,
    SecondProductId int not null,
    PRIMARY KEY (FirstProductId, SecondProductId)
)

通过这种方式，您可以使用下面的查询检索某些产品的所有相关产品：

In this way, you can retrieve all related products for some product with the query below:

SELECT SecondProductId
FROM ProductsRelation
WHERE FirstProductId = @ProductID

这篇关于设计相关产品表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！