How to represent a 2-D data matrix in a database

Question

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.

question from:https://stackoverflow.com/questions/1138777/how-to-represent-a-2-d-data-matrix-in-a-database

Answer 1

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.