在关系数据库中存储数组的最佳方法是什么？ [英] What's the best way to store an array in a relational database?

问题描述

我目前正在尝试设计一个数据库，但我不太确定处理对象中一个动态大小的数组字段的最佳方法。我的第一个想法是在对象中使用一列来存储整数数组。但是，我读得越多，我就越觉得这不是最好的选择。在具体示例方面，我有一个玩家对象，该对象存储0到许多项，这些项由整数表示。最好的表示方式是什么？

I'm currently trying to design a database and I'm not too sure about the best way to approach a dynamically sized array field of one of my objects. My first thought is to use a column in my object to store an array of integers. However the more I read, the more I think this isn't the best option. Concrete example wise, I have a player object that stores 0 to many items, which are represented by an integer. What is the best way to represent this?

推荐答案

如果该值的集合是原子，将它们存储在一起。这意味着，如果您始终关心整个组，则从不搜索嵌套值并且从不按嵌套值排序，则应将它们作为单个字段值存储在一起。

If that collection of values is atomic, store them together. Meaning, if you always care about the entire group, if you never search for nested values and never sort by nested values, then they should be stored together as a single field value.

如果没有，则应将它们存储在单独的表中，每个值都带有一行，每个值都分配有父ID（外键）在另一个拥有它们的表上的记录。

If not, they should be stored in a separate table, each value bring a row , each assigned the parent ID (foreign key) of a record on the other table that "owns" them as a group.

例如，来自科学仪器的大量读数只能一起用作分析的集合，应该一起存储在一个字段中。相比之下，可能经常需要查询单个电话号码的客户电话号码列表可能应该分解为相关子表中每行的单个电话号码。

For example, a clump of readings from a scientific instrument that are only ever used together as a collection for analysis should be stored together in a field. In contrast, a list of phone numbers for a customer that may often need to be queried for an individual number should probably be broken up into single phone number per row in a related child table.

有关更多信息，请搜索术语 数据库规范化。

For more info, search on the term "database normalization".

某些数据库以数据类型。例如， Postgres 允许您将一列定义为一个维数组，甚至是二维数组。

Some databases, support an array as a data type. For example, Postgres allows you to define a column as a one-dimension array, or even a two dimension array.

如果您的数据库不支持将数组作为一种列定义，那么您可能有三种选择：

If your database does not support array as a type of column definition, then you may have three alternatives:

• XML / JSON
  将数据收集转换为 XML 或 JSON 文档（如果您的数据库是数据库支持该类型。例如， Postgres 对存储，检索，以及使用XPath对XML进行非索引搜索。并且 Postgres 为JSON提供了出色的业界领先支持数据类型，包括对嵌套值的索引支持及其 jsonb 数据类型，其中传入的JSON被解析并以内部定义的二进制格式存储。此功能解决了人们考虑使用所谓的 NoSQL系统，试图存储和搜索半结构化数据。


• 文本
  创建数据的字符串表示形式，以文本形式存储。


• BLOB
  创建二进制值以存储为二进制大对象（BLOB）。

• XML/JSON
  Transform you data collection into an XML or JSON document if your database your database supports that type. For example, Postgres has basic support for storing, retrieving, and non-indexed searching of XML using XPath. And Postgres offers excellent industry-leading support for JSON as a data type including indexed support on nested values with its jsonb data type where incoming JSON is parsed and stored in an internally-defined binary format. This feature addresses one of the main reasons people consider using the so-called "NoSQL" systems, looking to store and search semi-structured data.
• Text
  Create a string representation of your data to store as text.
• BLOB
  Create a binary value to store as a binary large object (BLOB).

这篇关于在关系数据库中存储数组的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！