PostgreSQL - 整数[]最佳实践 [英] PostgreSQL - integer[] best practice

问题描述

最近在网络应用上工作我决定在数据模型中使用integer []。有2个表，一个有文章数据，另一个有标签（标签ID和描述），决定标签ID，文章将在article.tags integer []列中标记。

Working on a web app lately I decided to use integer[] in the data model. Having 2 tables, one with articles' data and a second with tags (tag id and description), decided to the tag ids an article would be tagged with in a article.tags integer[] column.

Milen A. Radev 指出：

提示：数组不是集合;搜索特定的数组元素可能是数据库错误设计的标志。考虑为每个将成为数组元素的项使用一个单独的表。这将更容易搜索，并且可能更好地扩展到大量元素。

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

不仅如此，但是必须使用JDBC和iBatis处理整数[]，我应该说有趣。

Not only that, but having to work with integer[] using JDBC and iBatis turned out, shall I say "interesting".

目前，我可以完成我必须做的工作实施。为了简单起见，它可能会使用一个存储article.id和tag.id关系的单独表来重新工作。

For the moment, I can get away with the working implementation in place for what I had to do. It will probably get re-worked for simplicity's sake using a separate table storing article.id and tag.id relationships.

最后我对什么是整数感到困惑[]最适合用于什么情况？

In the end I'm puzzled by what is integer[] best used for and in what context?

我想我已经找到了最好的方法。

I think I figured out the hard way what it's not best for.

推荐答案

恕我直言，因为任何阵列都违反了1NF，最好的背景是：...（鼓号）...... ..无。

IMHO, since any array is a violation of 1NF, the best context is:... (drumroll)..... none.

这就解决了为什么我们要拥有不打算查询的数据的问题。所有值都是可以搜索的，如果我们不应该在可搜索的值上使用数组，我们再次得出它永远不值得使用的结论。

This gets to the question of why we would have data that is not meant to be queried. All values are potentially searchable, and if we are not supposed to use an array on searchable values, we are again led to the conclusion it is never worth using.

这留下只是一个假设的情况，您只是为了在客户端上进行分析和操作而存储数组。我确定这些存在，但不符合我的经验。

This leaves only a hypothetical case where you are storing an array solely for the purpose of analysis and manipulation on the client. I'm sure these exist, but not in my experience.

编辑：上面我说，我确信这些存在.. 看看@mu对于一个例子来说太短了。

Above I said, "I'm sure these exist..." Look at @mu is too short's answer for an example.

这篇关于PostgreSQL - 整数[]最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！