存储和比较独特的组合 [英] Storing and comparing unique combinations

问题描述

我需要一个网站，除其他事项外，你应该能够选择多个类别的搜索功能。该搜索将存储在数据库中，但搜索参数每个唯一组合应该仅被存储一次，还包括选择的类别的独特组合。

问题是，我无法弄清楚如何存储选择的类别的组合。我已经看过阵列，发现这的http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/但显然该功能从未实现的。

所以，如果我需要使用多个表我想我可以有一个表有每个独特组合，可以被引用和容易比较和ID组合，然后另一个表链接类别的组合。但是，这种方法我怎么能检查一个组合已经存在？我能想到的唯一的办法是遍历所有现有的组合，并检查是否这些中的任何一个等于比组合。

我觉得这不可能是这样一个罕见的问题，但我无法找到这样做的例子。我也觉得我的方法可能不是最好的。任何建议都非常欢迎。

 我现在有这两个表：分类
 - 类别ID（INT）
 - 名称（字符串）搜索
 -  SearchId（INT）
 - 关键字（字符串）
 -  ExampleOption1（布尔）
 -  ExampleOption2（布尔）
 -  CategoriesCombinationId（INT） - 这将重新present类别和链路的独特组合的组合表
 

这是我怎么可能会尝试解决问题（如果有检查的组合已经存在的好方法）：

  CategoriesCombinations  - 独特的组合
 -  CombinationId（INT）CombinedCategories
 -  CombinationId（INT） - 链接标识的组合表
 - 类别ID（INT） - 链接ID在类别表
 

解决方案

存储为数组（非正规化）

我会考虑附加模块 intarray 提供方便（和快速）功能的uniq（）和排序（）。在一个典型的现代Postgres的安装很容易，因为：

 创建扩展intarray;
 

• How要在PostgreSQL中数组元素创建索引？

使用这些，一个简单的检查约束可以强制执行升是不同的元素的数组。

  CHECK（uniq的（排序（cat_arr））= cat_arr）
 

您可以的此外的（可选）有自动归数组值 ON INSERT或UPDATE触发器。然后，你可以通过的任何的阵列（可能是无序和愚弄），一切都只是工作。这样的：

  CREATE OR REPLACE FUNCTION trg_search_insup_bef（）
  返回一个触发器AS
$ FUNC $
开始
   NEW.cat_arr：= uniq的（排序（NEW.cat_arr）;
   回报新;
结束
$ FUNC $ LANGUAGE PLPGSQL;CREATE TRIGGER insup_bef
BEFORE INSERT或UPDATE cat_arr对搜索
FOR EACH ROW
EXECUTE PROCEDURE trg_search_insup_bef（）;
 

附加模块intarray是可选的，还有其他的方式：

• Ordering通过聚合函数（第一个值），另一列
不同的列值

但intarray功能提供卓越的性能。

然后你可以创建一个 UNIQUE 约束的阵列列执行整个阵列的唯一性。

  UNIQUE（cat_arr）
 

我写了更多关于结合（不可靠，但更方便），在这个相关答案触发就在两天前（非常严格和可靠的）限制的优点：

• Cross PostgreSQL中
表约束

如果，每个组合，所有你需要每个类别来存储是ID（没有其他信息），这应该是足够好了。结果
然而，参照完整性不易保证这种方式。有数组元素的外键约束（还） - 喜欢的记录在您的链接：如果其中一类被删除或更改标识，引用突破......

标准化模式

如果你需要存储更多的还是你宁愿用一个标准化的模式去实施参照完整性，或出于某种原因，你能做到这一点，也和添加一个触发器来填充一个手工制作的物化视图（冗余表），并执行以类似的方式唯一性：

  CREATE TABLE搜索（
  SEARCH_ID串行PRIMARY KEY
...更多列
）;CREATE TABLE猫（
  CAT_ID串行PRIMARY KEY
，猫文字NOT NULL
）;CREATE TABLE search_cat（
  SEARCH_ID INT资料搜索ON DELETE CASCADE
，CAT_ID诠释参考猫
，PRIMARY KEY（SEARCH_ID，CAT_ID）
）;
 

相关答案（而不是独特的组合，但独特元素），演示触发器：

• Can PostgreSQL的对数组元素的唯一性约束？

I need search functionality on a website where among other things you should be able to select multiple categories. The searches will be stored in the database but each unique combination of search parameters should only be stored once, this also includes the unique combination of selected categories.

The problem is that I cannot figure out how to store the combinations of selected categories. I have looked at arrays and found this http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ but apparently that feature was never implemented.

So if I need to use multiple tables I was thinking I could have one table for the combinations that has an id for each unique combination that can be referenced and compared easily and then another table linking the categories to the combination. But with this approach how can I check if a combination already exists? The only way I can think of is to loop through all existing combinations and check if any one of those equal the compared combination.

I feel like this cannot be such an uncommon problem but I can't find any examples that do this. I also feel like my approach may not be the best. Any suggestions are very much welcome.

I have these two tables currently:

Categories
- CategoryId (int)
- Name (string)

Searches
- SearchId (int)
- Keywords (string)
- ExampleOption1 (bool)
- ExampleOption2 (bool)
- CategoriesCombinationId (int) -- this would represent the unique combination of categories and links to the combination table

And this is how I might try to solve the problem (if there is a good way to check if a combination already exists):

CategoriesCombinations -- unique combinations
- CombinationId (int)

CombinedCategories
- CombinationId (int) -- links to id in combinations table
- CategoryId (int) -- links to id in categories table

解决方案

Store as array (denormalized)

I would consider the additional module intarray that provides the convenient (and fast) functions uniq() and sort(). In a typical modern Postgres installation it's as easy as:

CREATE EXTENSION intarray;

• How to create an index for elements of an array in PostgreSQL?

Using these, a simple CHECK constraint can enforce ascending arrays with distinct elements.

CHECK (uniq(sort(cat_arr)) = cat_arr)

You can additionally (optionally) have a trigger that normalizes array values ON INSERT OR UPDATE automatically. Then you can just pass any array (possibly unsorted and with dupes) and everything just works. Like:

CREATE OR REPLACE FUNCTION trg_search_insup_bef()
  RETURNS trigger AS
$func$
BEGIN
   NEW.cat_arr := uniq(sort(NEW.cat_arr);
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE OF cat_arr ON search
FOR EACH ROW
EXECUTE PROCEDURE trg_search_insup_bef();

The additional module intarray is optional, there are other ways:

• Ordering distinct column values by (first value of) other column in aggregate function

But the intarray functions deliver superior performance.

Then you can just create a UNIQUE constraint on the array column to enforce uniqueness of the whole array.

UNIQUE (cat_arr)

I wrote more about the advantages of combining (very strict and reliable) constraints with (less reliable but more convenient) triggers in this related answer just two days ago:

• Cross table constraints in PostgreSQL

If, for each combination, all you need to store per category is the ID (and no additional info), this should be good enough.
However, referential integrity is not easily ensured this way. There are no foreign key constraints for array elements (yet) - like documented in your link: If one of the categories is deleted or you change IDs, references break ...

Normalized schema

If you need to store more or you'd rather go with a normalized schema to enforce referential integrity or for some reason, you can do that, too, and add a trigger to populate a hand-made materialized view (a redundant table) and enforce uniqueness in a similar way:

CREATE TABLE search (
  search_id serial PRIMARY KEY
, ... more columns
);

CREATE TABLE cat (
  cat_id serial PRIMARY KEY
, cat text NOT NULL
);

CREATE TABLE search_cat (
  search_id int REFERENCES search ON DELETE CASCADE
, cat_id    int REFERENCES cat
, PRIMARY KEY (search_id, cat_id)
);

Related answer (not for unique combinations, but for unique elements) that demonstrates the trigger:

• Can PostgreSQL have a uniqueness constraint on array elements?

这篇关于存储和比较独特的组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！