database 如何在数据库中表示二维数据矩阵
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1138777/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to represent a 2-D data matrix in a database
提问by miguel
I have a data set which consists of an ID and a matrix (n x n) of data related to that ID.
我有一个数据集，它由一个 ID 和一个与该 ID 相关的数据矩阵 (nxn) 组成。
Both the column names (A,B,C,D) and the Row names (1,2,3) are also important and need to be held for each individual ID, as well as the data (a1,b1,c1,d1,...)
列名 (A,B,C,D) 和行名 (1,2,3) 也很重要，需要为每个单独的 ID 以及数据 (a1,b1,c1,d1 ,...)
for example:
例如：
ID | A | B | C | D |
身 | 一个 | 乙 | C | D |
1 | a1 | b1 | c1 | d1 |
1 | a1 | b1 | c1 | d1 |
2 | ... | ... | ... | ... |
2 | ... | ... | ... | ... |
3 | ... | ... | ... | ... |
3 | ... | ... | ... | ... |
I am trying to determine the best way of modelling this data set in a database, however, it seems like something that is difficult given the flat nature of RDBMS.
我正在尝试确定在数据库中对该数据集进行建模的最佳方式，但是，鉴于 RDBMS 的扁平性质，这似乎有些困难。
Am I better off holding the ID and an XML blob representing the data matrix, or am i overlooking a simpler solution here.
我最好持有 ID 和代表数据矩阵的 XML blob，还是我在这里忽略了一个更简单的解决方案。
Thanks.
谢谢。
采纳答案by chaos
RDBMSes aren't flat. The R part sees to that. What you need is:
RDBMS 不是扁平的。R 部分负责这一点。你需要的是：
Table Entity
------------
ID
Table EntityData
----------------
EntityID
MatrixRow (1, 2, 3...)
MatrixColumn (A, B, C, D...)
Value
Entity:EntityDatais a one-to-many relationship; each cell in the matrix has an EntityData row.
Entity:EntityData是一对多的关系；矩阵中的每个单元格都有一个 EntityData 行。
Now you have a schema that can be analyzed at the SQL level, instead of just being a data dump where you have to pull and extract everything at the application level in order to find out anything about it.
现在您拥有了一个可以在 SQL 级别进行分析的模式，而不仅仅是一个数据转储，您必须在应用程序级别拉取和提取所有内容才能找到有关它的任何信息。
回答by jdkoftinoff
This is one of the reasons why PostgreSQL supports arrays as a data type. See
这是 PostgreSQL 支持数组作为数据类型的原因之一。看
- http://www.postgresql.org/docs/8.4/static/functions-array.htmland
- http://www.postgresql.org/docs/8.4/static/arrays.html
- http://www.postgresql.org/docs/8.4/static/functions-array.html和
- http://www.postgresql.org/docs/8.4/static/arrays.html
Where it shows you can use syntax like ARRAY[[1,2,3],[4,5,6],[7,8,9]]to define the values of a 3x3 matrix or val integer[3][3]to declare a column type to be a 3x3 matrix.
在它显示的地方，您可以使用类似ARRAY[[1,2,3],[4,5,6],[7,8,9]]定义 3x3 矩阵的值或val integer[3][3]将列类型声明为3x3 矩阵的语法。
Of course this is not at all standard SQL and is PostgreSQL specific. Other databases may have similar-but-slightly-different implementations.
当然，这根本不是标准的 SQL，而是特定于 PostgreSQL 的。其他数据库可能有类似但略有不同的实现。
回答by Draemon
If you want a truly relational solution:
如果你想要一个真正的关系解决方案：
Matrix
------
id
Matrix_Cell
-----------
matrix_id
row
col
value
But constraints to make sure you had valid data would be hideous.
但是确保您拥有有效数据的约束将是可怕的。
I would consider a matrix as a single value as far as the DB is concerned and store it as csv:
就数据库而言，我会将矩阵视为单个值并将其存储为 csv：
Matrix
------
id
cols
data
Which is somewhat lighter than XML.
这比 XML 轻一些。
回答by Lee
I'd probably implement it like this:
我可能会像这样实现它：
Table MatrixData
----------------
id
rowName
columnName
datapoint
If all you're looking for is storing the data, this structure will hold any size matrix and allow you to reconstitute any matrix from the ID. You will need some post-processing to present it in "matrix format", but that's what the front-end code is for.
如果您要查找的只是存储数据，则此结构将保存任何大小的矩阵，并允许您根据 ID 重建任何矩阵。您将需要一些后处理以“矩阵格式”呈现它，但这就是前端代码的用途。
回答by djangofan
can the data be thought of as "row data"? if so then maybe you could store each row as a Object (or XML Blob) with data A,B,C,D and then, in your "representation", you use something like a LinkedHashMap (assuming Java) to get the objects with an ID key.
可以将数据视为“行数据”吗？如果是这样，那么也许您可以将每一行存储为带有数据 A、B、C、D 的对象（或 XML Blob），然后在您的“表示”中，您使用类似 LinkedHashMap（假设为 Java）的东西来获取对象身钥匙。
Also, it seems that by its very basic nature, a typical database table already does what you need doesn't it?
此外，似乎就其非常基本的性质而言，典型的数据库表已经可以满足您的需求，不是吗？
回答by Mahesh Gupta
Or even better what you can do is, create a logical array like structure. Say u want to store an m X n array.. Create m attributes in the table. In each attribute store n elements separated by delimiters ...
或者甚至更好的是，您可以创建一个类似结构的逻辑数组。假设你想存储一个 m X n 数组。在表中创建 m 个属性。在每个属性中存储 n 个由分隔符分隔的元素......
while retrieving the data, simply do reverse parsing to easily get back the data..
在检索数据时，只需进行反向解析即可轻松取回数据。
