如何在数据库中表示2-D数据矩阵 [英] How to represent a 2-D data matrix in a database

问题描述

我有一个数据集，其中包含一个ID和与该ID相关的数据矩阵（nxn）。

列名（A，B， （a1，b1，c1，d1，...）以及行名（1,2,3）也是重要的，并且需要为每个单独的ID保持。

例如：

A | B | C | D |

1 | a1 | b1 | c1 | d1 |

2 | ... | ... | ... | ... |

3 | ... | ... | ... | ... |

我试图确定在数据库中建模这个数据集的最佳方法，但是， RDBMS。

我最好拿着ID和一个代表数据矩阵的XML blob，或者我可以在这里忽略一个更简单的解决方案。

谢谢。

解决方案

RDBMS不是平的。 R部分看到了。您需要的是：

 表实体
 ------------ 
 ID 
 
表EntityData 
 ---------------- 
 EntityID 
 MatrixRow（1，2，3 .. 。）
 MatrixColumn（A，B，C，D ...）
值
  

实体：EntityData 是一对多关系;每个单元格都有一个EntityData行。

现在你有一个可以在SQL层进行分析的模式，而不仅仅是一个数据转储，你必须在应用程序级别拉取和提取一切，以了解任何东西。

I have a data set which consists of an ID and a matrix (n x n) of data related to that ID.

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,...)

for example:

ID | A | B | C | D |

1 | a1 | b1 | c1 | d1 |

2 | ... | ... | ... | ... |

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.

Am I better off holding the ID and an XML blob representing the data matrix, or am i overlooking a simpler solution here.

Thanks.

解决方案

RDBMSes aren't flat. The R part sees to that. What you need is:

Table Entity
------------
ID

Table EntityData
----------------
EntityID
MatrixRow (1, 2, 3...)
MatrixColumn (A, B, C, D...)
Value

Entity:EntityData is a one-to-many relationship; each cell in the matrix has an EntityData row.

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.

这篇关于如何在数据库中表示2-D数据矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！