在数据库中使用数组是不好的设计吗？ [英] Is it bad design to use arrays within a database?

问题描述

所以我正在为一个个人项目创建一个数据库，只是为了使用PostgreSQL和某些可以使用PostgreSQL数据库的语言和应用程序来获取更多信息。

我已经认识到使用数组不一定是兼容的（数组不是原子的，对吧？）与1NF。所以我的问题是：这种方式是否缺乏效率或数据安全？

p>

稍长的答案：

您应该在适当的时候学会使用数组。数组不是坏的设计本身，它们作为字符变化字段（字符数组，没有？）的原子，它们的存在使我们的生活更轻松，我们的数据库更快更轻。考虑到可移植性（大多数数据库系统不支持数组，或者以不同于Postgres的方式这样做）有一些问题。

示例：

您有一篇包含帖子和标签的博客，每个帖子可能有0个或更多标签。首先要考虑的是创建一个包含两列 postid 和 tagid 的不同表格，并分配标签那个表。

如果我们需要使用tagid搜索帖子，那么额外的表是必要的（当然有适当的索引）。

但是，如果我们只想让标签信息显示为帖子的额外信息，那么我们可以轻松地在帖子表中添加一个整数数组列，并从中提取信息。这仍然可以使用额外的表，但使用数组减少了数据库的大小（不需要额外的表或额外的行），并简化查询，让我们执行我们的select查询，加入少一个表，似乎更容易理解人的眼睛（最后一部分是在旁观者的眼睛，但我认为我在这里说的多数）。

这个例子可能很差，但它是第一个想到的。

结束：

数组不是必需的。如果你使用错误，它们可能是有害的。你可以没有他们生活，并有一个伟大的，快速和优化的数据库。当你考虑可移植性（例如重写你的系统以使用其他数据库），那么你不能使用数组。

如果你确定你会坚持使用Postgres，那么你可以安全地使用你认为合适的数组。它们存在的原因，既不是坏的设计也不是不合规。当你在正确的地方使用它们时，他们可以帮助一点点简单的数据库结构和你的代码，以及空间和速度优化。这就是全部。

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.

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?

解决方案

Short answer to the title: No

A bit longer answer:

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)

Example:

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.

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.

Conclusion:

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.

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屋！