在数据库中使用数组是一种糟糕的设计吗? [英] Is it bad design to use arrays within a database?

问题描述

所以我正在为个人项目制作一个数据库，只是为了让我对 PostgreSQL 以及可以使用 PostgreSQL 数据库的某些语言和应用程序更加了解.

So I'm making a database for a personal project just to get more than my feet wet with PostgreSQL and certain languages and applications that can use a PostgreSQL database.

我已经意识到使用数组不一定符合 1NF(数组不是原子的，对吧?).所以我的问题是:这种方式是否缺乏效率或数据安全?我应该早点学会不使用数组吗?

I've come to the realization that using an array isn't necessarily even compliant (Arrays are not atomic, right?) with 1NF. So my question is: Is there a lack of efficiency or data safety this way? Should I learn early to not use arrays?

推荐答案

对标题的简短回答:否

答案有点长:

你应该学会在适当的时候使用数组.数组本身并不是一个糟糕的设计，它们就像一个字符变化字段(字符数组，不是吗?)存在考虑可移植性的问题(大多数数据库系统不支持数组，或者以与 Postgres 不同的方式支持)

You should learn to use arrays when appropriate. Arrays are not bad design themselves, they are as atomic as a character varying field (array of characters, no?) and they exists to make our lives easier and our databases faster and lighter. There are issues considering portability (most database systems don't support arrays, or do so in a different way than Postgres)

示例:

您有一个包含帖子和标签的博客，每篇帖子可能有 0 个或多个标签.想到的第一件事是制作一个包含两列 postid 和 tagid 的不同表，并在该表中分配标签.

You have a blog with posts and tags, and each post may have 0 or more tags. The first thing that comes to mind is to make a different table with two columns postid and tagid and assign the tags in that table.

如果我们需要搜索带有 tagid 的帖子，那么额外的表是必要的(当然还有适当的索引).

If we need to search through posts with tagid, then the extra table is necessary (with appropriate indexes of course).

但如果我们只想将标签信息显示为帖子的额外信息，那么我们可以轻松地在帖子表中添加一个整数数组列并从中提取信息.这仍然可以用额外的表来完成，但使用数组减少了数据库的大小(不需要额外的表或额外的行)并通过让我们通过加入一个更少的表来执行我们的选择查询来简化查询，并且看起来更容易理解通过人眼(最后一部分是在旁观者的眼中，但我想我在这里代表大多数).如果我们的标签是预加载的，那么甚至不需要一个连接.

But if we only want the tag information to be shown as the post's extra info, then we can easily add an integer array column in the table of posts and extract the information from there. This can still be done with the extra table, but using an array reduces the size of the database (no needed extra tables or extra rows) and simplifies the query by letting us execute our select queries with joining one less table and seems easier to understand by human eye (the last part is in the eye of the beholder, but I think I speak for a majority here). If our tags are preloaded, then not even one join is necessary.

这个例子可能很糟糕，但它是第一个想到的.

The example may be poor but it's the first that came to mind.

结论:

不需要数组.如果您错误地使用它们，它们可能是有害的.您可以在没有它们的情况下生活，并拥有一个出色、快速和优化的数据库.当您考虑可移植性(例如重写您的系统以与其他数据库一起工作)时，您不得使用数组.

Arrays are not necessary. They can be harmful if you use them wrong. You can live without them and have a great, fast and optimized database. When you are considering portability (e.g. rewriting your system to work with other databses) then you must not use arrays.

如果你确定你会坚持使用 Postgres，那么你可以安全地在你认为合适的地方使用数组.它们存在是有原因的，既不是糟糕的设计，也不是不合规的.当你在正确的地方使用它们时，它们可以帮助简化数据库结构和代码，以及空间和速度优化.仅此而已.

If you are sure you'll stick with Postgres, then you can safely use arrays where you find appropriate. They exist for a reason and are neither bad design nor non-compliant. When you use them in the right places, they can help a little with simplicity of database structures and your code, as well as space and speed optimization. That is all.

这篇关于在数据库中使用数组是一种糟糕的设计吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！