C# database-design rdbms relational-database

匹配系统的表设计

发布于 2024-09-11 03:15:29 字数 475 浏览 7 评论 0原文

我将如何为匹配系统设计表格，执行如下操作：

我们有 X 个产品，它们可以具有 Y 属性。属性的数量可能因产品而异，并且两种产品不必具有任何相似的属性。

我的首要目标是选择一种产品并找到最相似的产品。

通常我会这样做：

create table products
(
   id serial not null primary key,
   name varchar(40)
)

create table properties
(
  id serial not null primary key,
  name varchar(40)
)

create table product_properties
(
  product_id int not null,
  property_id int not null
)

我能想到的唯一方法是循环所有产品，在循环中获取它们的属性并与源产品进行比较。但这似乎不太有效。

原文

How would I design the tables for a matching system doing something like this:

We have X products which can have Y properties. The number of properties can vary from product to product and two products doesn't have to have any similar properties.

My number one goal is to take one product and find the most similar product.

Normally I would do something like this:

create table products
(
   id serial not null primary key,
   name varchar(40)
)

create table properties
(
  id serial not null primary key,
  name varchar(40)
)

create table product_properties
(
  product_id int not null,
  property_id int not null
)

The only way I can think of is to loop through all products, fetch their properties in the loop and compare with the source product. But that doesn't seem very effective.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

潜移默化 2024-09-18 03:15:30

这样的事情会有帮助吗？

SELECT *
    FROM products
    WHERE id <> <given product id>
    ORDER BY (SELECT COUNT(*)
                  FROM product_properties
                  WHERE product_properties.product_id = products.id AND
                        product_properties.property_id IN
                                (SELECT property_id
                                     FROM product_property
                                     WHERE product_id = <given product id>))
    LIMIT 1;

这样做的目的是为了获得与您的原始产品具有最多共同特性的产品。

Would something like this be any help?

SELECT *
    FROM products
    WHERE id <> <given product id>
    ORDER BY (SELECT COUNT(*)
                  FROM product_properties
                  WHERE product_properties.product_id = products.id AND
                        product_properties.property_id IN
                                (SELECT property_id
                                     FROM product_property
                                     WHERE product_id = <given product id>))
    LIMIT 1;

This is intended to get the product that has the most properties in common with your original product.

回复收藏 0 原文

~没有更多了~